sql - 计算重复率队列的百分比

我编写了一个重复率查询，以以下格式提供队列重复率数据：

cohort_join_day | repeat_day | repeat_users
11/15/16        |      0     | 10000
11/15/16        |      1     | 6000
11/15/16        |      2     | 3000

重复第0天表示当天的总队列大小
我试图跳过一个excel步骤，在第四列中添加每日重复率百分比，如下所示：

cohort_join_day | repeat_day | repeat_users | repeat_percentage
11/15/16        |      0     | 10000        | 100%
11/15/16        |      1     | 6000         |  60%
11/15/16        |      2     | 3000         |  30%

此行的计算应该非常简单，例如：
第1天第6天的队列重复率=（第1天第6天的队列重复率）/（第1天第0天的队列重复率）
（第1天队列在第0天的重复率）表示队列的总规模
最好的方法是什么？
下面是我写的每日重复率查询：

    SELECT
  to_char(cohort_join_day, 'YYYY-MM-DD')            AS cohort_join_day,
  EXTRACT(DAY FROM (current_day - cohort_join_day)) AS repeat_day,
  COUNT(DISTINCT unique_id)                         AS repeat_users
FROM
  (
    SELECT
      auu.unique_id,
      date_trunc('day', auu.ds) AS current_day,
      date_trunc('day', fsb.ds) AS cohort_join_day
    FROM rust.a_unique_users AS auu
      JOIN mobile.first_seen_byos AS fsb
        ON fsb.unique_id = auu.unique_id
    WHERE
      auu.os_type = 'iphone_native_app'
      AND fsb.ds >= '2016-11-01'
  ) AS uniques_by_day
WHERE
  cohort_join_day <= current_day
GROUP BY
  cohort_join_day,
  repeat_day;

最佳答案

SELECT
    *
    ,(repeat_users * 100.0) /
       MAX(CASE WHEN repeat_day = 0 THEN repeat_users END) OVER () as repeat_percentage
FROM
     Table

条件聚合和窗口函数使这更容易
如果你想每天都做这个计算，那么用CORORIGONEXYLYDY分区窗口函数：

SELECT
    *
    ,(repeat_users * 100.0) /
       MAX(CASE WHEN repeat_day = 0 THEN repeat_users END) OVER (PARTITION BY cohort_join_day) as repeat_percentage
FROM
    Table

MAX(column) OVER ()只需在整个数据集中提供MAX中的column值。
MAX(column) OVER (PARTITION BY column2)将在该列中为匹配的MAX值提供column2值。你可以认为PARTITION BY类似于GROUP BY。
用case表达式替换column可以进行条件聚合。例如，当您只希望repeat_users when repeat_day = 0a case表达式表示它将意味着每个分区只返回1个值，而忽略其他值，因为它们将为空。
因此，如果您想在不使用窗口函数的直接查询中执行相同的操作，您可以执行以下操作：

SELECT
    t.*
    ,(t.repeat_users * 100.0) / (SELECT t2.repeat_users
             FROM
                Table t2
             WHERE
                t.cohort_join_day = t2.cohort_join_day
                AND t2.repeat_day = 0)     as repeat_percentage
FROM
    Table t

为了向您展示如何使用Juan Carlo的方法，当您有多天的时间时，您可以这样做：

WITH cte AS (
    SELECT
       cohort_join_day
       ,repeat_users
    FROM
       @Table
    WHERE
       repeat_day = 0
)

SELECT
    t.*
    ,(t.repeat_users * 100.0) / c.repeat_users as repeat_percentage
FROM
    Table t
    CROSS JOIN cte c
WHERE
    t.cohort_join_day = c.cohort_join_day

如果你想参加跑步总决赛

SUM(column) OVER (PARTITION BY column2 ORDER BY column3)

一定要熟悉窗口功能，现在他们是救命稻草。

关于sql - 计算重复率队列的百分比，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40899995/