计算 SQL Query 中两列之间的百分比作为每天的另一列

角木野

我正在尝试计算No_of_daily_installs.

题：

有人能解释我如何可以添加代表的日常PLIT％的新列No_of_daily_installs每LAT_type为％？

我现在所拥有的：

|-----------|-------------------|--------------------|-----------------------|
|Insall_Date|Lat_type           |No_of_daily_installs| RunningTotal_Installs |
|*---------*|*-----------------*|*------------------*|*---------------------*|
|2021-06-30 |Ad Tracking Enabled|   613              |21345                  |
|2021-06-30 |Limit Ad Tracking  |  3723              |74273                  |
|2021-06-29 |Limit Ad Tracking  |  3553              |70550                  |
|2021-06-29 |Ad Tracking Enabled|   480              |20732                  |
|2021-06-28 |Limit Ad Tracking  |  2869              |66997                  |
|2021-06-28 |Ad Tracking Enabled|   375              |20252                  |

我想达到的目标：

|-----------|-------------------|--------------------|--------------|-----------------------
|Insall_Date|Lat_type           |No_of_daily_installs|%_of_daily_LAT| RunningTotal_Installs |
|*---------*|*-----------------*|*------------------*|*------------*|*---------------------*|
|2021-06-30 |Ad Tracking Enabled|   613              |0.15          |21345                  |
|2021-06-30 |Limit Ad Tracking  |  3723              |0.85          |74273                  |
|2021-06-29 |Limit Ad Tracking  |  3553              |0.80          |70550                  |
|2021-06-29 |Ad Tracking Enabled|   480              |0.20          |20732                  |
|2021-06-28 |Limit Ad Tracking  |  2869              |0.85          |66997                  |
|2021-06-28 |Ad Tracking Enabled|   375              |0.15          |20252                  |

到目前为止我的代码：

WITH "Adtracking" (
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs"
) AS (
    SELECT
        to_date("created_at") date_install,
        CASE WHEN "tracking_limited" = '1' THEN
            'Limit Ad Tracking'
        ELSE
            'Ad Tracking Enabled'
        END AS "Ad Tracking",
        count("tracking_limited") AS "number_of_occurences"
    FROM
        TEMP_DB.DATA_LAKE.ADJUST_CSV_DATA
    WHERE
        TRUE
        AND "platform" = 'mobile_app'
        AND "activity_kind" = 'install'
        AND "os_name" = 'ios'
    GROUP BY
        1,
        2
    ORDER BY
        1,
        2
)
SELECT
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs",
    SUM("No_of_daily_installs") OVER (PARTITION BY "Lat_type" ORDER BY "Install_Date") AS "RunningTotal_Installs"
FROM
    "Adtracking"
WHERE
    "Install_Date" ILIKE '2021-06%'
GROUP BY
    1,
    2,
    3
ORDER BY
    1,
    2

戈登

下面将每种 Lat 类型的每日安装量除以每个安装日期的每日安装量总数/总和，最后将其四舍五入到小数点后两位。

ROUND("No_of_daily_installs"/(SUM("No_of_daily_installs") OVER (PARTITION BY "Install_Date")),2) AS "%_of_daily_LAT"

您可以尝试完整的示例：

WITH "Adtracking" (
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs"
) AS (
    SELECT
        to_date("created_at") date_install,
        CASE WHEN "tracking_limited" = '1' THEN
            'Limit Ad Tracking'
        ELSE
            'Ad Tracking Enabled'
        END AS "Ad Tracking",
        count("tracking_limited") AS "number_of_occurences"
    FROM
        TEMP_DB.DATA_LAKE.ADJUST_CSV_DATA
    WHERE
        TRUE
        AND "platform" = 'mobile_app'
        AND "activity_kind" = 'install'
        AND "os_name" = 'ios'
    GROUP BY
        1,
        2
    ORDER BY
        1,
        2
)
SELECT
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs",
-- modification begins
    ROUND("No_of_daily_installs"/(SUM("No_of_daily_installs") OVER (PARTITION BY "Install_Date")),2) AS "%_of_daily_LAT"
-- modification ends
    SUM("No_of_daily_installs") OVER (PARTITION BY "Lat_type" ORDER BY "Install_Date") AS "RunningTotal_Installs"
FROM
    "Adtracking"
WHERE
    "Install_Date" ILIKE '2021-06%'
GROUP BY
    1,
    2,
    3
ORDER BY
    1,
    2

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。