在Amazon Redshift上计算DAU和MAU时修复MAU问题 [英] Fix the MAU problem while calculating DAU and MAU on Amazon Redshift

问题描述

根据此，我正在使用以下查询来计算MAU和DAU a>帖子:

I am using the following query to calculate MAU and DAU, according to this post:

WITH dau AS
(
  SELECT TRUNC(created_at) AS created_at,
         COUNT(DISTINCT member_id) AS dau
  FROM table ds
  WHERE ds.created_at BETWEEN '2018-09-03' AND '2018-09-08'
  GROUP BY TRUNC(created_at)
)
SELECT created_at,
       dau,
       (SELECT COUNT(DISTINCT member_id)
        FROM table ds
        WHERE ds.created_at BETWEEN created_at - 29*INTERVAL '1 day' AND created_at) AS mau
FROM dau
ORDER BY created_at

我尝试运行此查询并获得以下结果:

I try running this query and get the following results:

2018-09-03  12844   3976132
2018-09-04  54236   3976132
2018-09-05  58631   3976132
2018-09-06  59786   3976132
2018-09-07  52317   3976132
2018-09-08  4   3976132

可以清楚地看到MAU列具有重复值. 我该如何解决?任何指针都会有所帮助.

It can be clearly seen that the MAU column has repeating values. How do I fix this? Any pointers would be helpful.

推荐答案

您应在列名称前添加前缀:

You should prefix column names:

WITH dau AS
(
  SELECT TRUNC(created_at) AS created_at,
         COUNT(DISTINCT member_id) AS dau
  FROM table ds
  WHERE ds.created_at BETWEEN '2018-09-03' AND '2018-09-08'
  GROUP BY TRUNC(created_at)
)
SELECT created_at,
       dau,
       (SELECT COUNT(DISTINCT member_id)
        FROM table ds
        WHERE ds.created_at 
          BETWEEN dau.created_at - 29*INTERVAL '1 day' AND dau.created_at) AS mau
          -- here
FROM dau
ORDER BY created_at

或:

SELECT TRUNC(created_at) AS created_at,
     COUNT(DISTINCT member_id) AS dau,
     COUNT(DISTINCT member_id) 
     FILTER(WHERE TRUNC(created_at)>=TRUNC(created_at)-29*INTERVAL '1 day') AS mau
FROM table ds
WHERE ds.created_at BETWEEN '2018-09-03' AND '2018-09-08'
GROUP BY TRUNC(created_at)
ORDER BY created_at

这篇关于在Amazon Redshift上计算DAU和MAU时修复MAU问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！