sql - 分段的 DAU/MAU 计算

Question

我编写了以下查询来计算 DAU/MAU 比率:

WITH dau AS
(
  SELECT TRUNC(created_at) AS created_at,
         CASE
           WHEN user_agent SIMILAR TO '%(Mobile|iPhone|iPod|iPad|Android)%' THEN 'non-desktop'
           ELSE 'desktop'
         END AS trafficsource,
         COUNT(DISTINCT member_id) AS dau
  FROM table ds
  WHERE ds.created_at BETWEEN '2017-01-01' AND '2017-12-11'
  AND   member_id <> 2
  AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
  GROUP BY TRUNC(created_at),
           trafficsource
)
SELECT created_at,
       trafficsource,
       dau,
       (SELECT COUNT(DISTINCT member_id)
        FROM table ds
        WHERE member_id <> 2
        AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
        AND   ds.created_at BETWEEN dau.created_at - 29*INTERVAL '1 day' AND dau.created_at) AS mau,
       (dau / CAST(mau AS float)) AS "DAU/MAU",
       (dau / CAST(mau AS float))*30 AS DaysOutOf30
FROM dau
WHERE EXTRACT(dayofweek FROM created_at) NOT IN (0,6)
AND   EXTRACT(month FROM created_at) NOT IN (5,6,7)
ORDER BY created_at

此查询为“桌面”和“非桌面”创建 2 个段。但值得注意的是，查询在同一天为这两个分割返回相同的“MAU”数字，如下所示。

created_at    trafficsource    dau    mau    DAU/MAU    DaysOutOf30
2017-01-02  desktop 4157    140834  0.02951702003777497 0.8855106011332491
2017-01-02  non-desktop 801 140834  0.005687547041197438    0.17062641123592315
2017-01-03  desktop 12610   140468  0.089771335820258   2.6931400746077396
2017-01-03  non-desktop 2891    140468  0.020581199988609505    0.6174359996582851
2017-01-04  non-desktop 4033    137516  0.029327496436778264    0.8798248931033479
2017-01-04  desktop 17902   137516  0.1301812152767678  3.9054364583030337

如何修复查询以返回创建的分段的“MAU”值？任何帮助将不胜感激。

Answer 1

您可以通过单独计算然后将其连接回 dau 来获得准确的 mau 值以获得最终结果。

逻辑步骤:

1. 每天为每个 member_id 创建 1 行流量。请注意，该范围的开始时间比您的最终选择范围早 30 天。
2. 创建基本保持不变的dau
3. 通过使用范围连接将流量连接到自身来创建mau，该范围连接(使用您的间隔逻辑)会引入最近 30 天的数据。对该数据中不同的 member_id 进行计数。
4. 选择最终数据，在 created_at 上连接 dau 和 mau。

<小时/>

WITH traffic AS (
    SELECT member_id,
           TRUNC(created_at) AS created_at,
           CASE WHEN user_agent SIMILAR TO '%(Mobile|iPhone|iPod|iPad|Android)%' 
           THEN 'non-desktop' ELSE 'desktop' END AS trafficsource,
    FROM table ds
    WHERE ds.created_at BETWEEN '2016-12-01' AND '2017-12-11'
    AND   member_id <> 2
    AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
    GROUP BY 1,2,3
), dau AS (
    SELECT created_at, trafficsource,
           COUNT(DISTINCT member_id) AS dau
    FROM traffic
    GROUP BY 1,2
), mau AS (
    SELECT ds.created_at, ds.trafficsource,
           COUNT(DISTINCT ds.member_id) AS mau
    FROM traffic ds
    LEFT JOIN traffic mth
        ON  ds.member_id = mth.member_id
        AND ds.created_at BETWEEN mth.created_at - 29*INTERVAL '1 day' AND mth.created_at
    GROUP BY 1,2
    )
SELECT created_at, trafficsource, dau, mau,
       (dau / CAST(mau AS float)) AS "DAU/MAU",
       (dau / CAST(mau AS float))*30 AS DaysOutOf30
FROM dau
JOIN mau    
    USING (created_at, trafficsource)
WHERE created_at BETWEEN '2017-01-01' AND '2017-12-11'
AND   EXTRACT(dayofweek FROM created_at) NOT IN (0,6)
AND   EXTRACT(month FROM created_at) NOT IN (5,6,7)
ORDER BY created_at
;