主页 / 问题 / 1170430
Accepted
Ayo
Asked:2020-08-26 00:06:59 +0000 UTC2020-08-26 00:06:59 +0000 UTC

如何完成查询以获取按月分组的 AVG(COUNT) 查询？

  • 772

有一个包含 user_id 和 server_upload_time 字段的事件表（一行 = 一个事件）。我需要得到一个包含日期和 avg_dau 字段的表格，其中日期是一周，而 avg_dau 是一周中按天数计算的平均值。换句话说，我们需要每周平均 dau 图表。

我设法找到了这个月的平均 dau：

select avg(users) as "avg_dau"
from (select count(distinct user_id) as "users",
        server_upload_time::date
    from event
    where server_upload_time::date between '2020-01-01' and '2020-02-01'
    group by 2)

但这是一行，如何更改查询以获得如下内容：

日期 | avg_dau

2020-01-01 | 140000

2020-01-08 | 138000

2020-01-15 | 142000

那些。在这里，您需要以某种方式在子查询中保存每周周期，并且在主查询中您已经可以使用例如一年。

更新。我加了一点，结果是这样的：

select date_trunc('Week', server_upload_time) as "date",
    count(distinct user_id) as "mau",

(select avg(users)
from (select count(distinct user_id) as "users",
        server_upload_time::date
    from event
    where server_upload_time::date between '2020-01-01' and '2020-02-01'
    group by 2)) as "avg_dau"
    
from event

where server_upload_time::date between '2020-01-01' and '2020-02-01'

group by 1

输出是周列表，不同的 mau 值，但子查询中的 avg_dau 不被视为每周的单独结果，而是作为 date 中描述的整个期间的平均值。是的，这是合乎逻辑的，但我不需要它:)

更新。 http://sqlfiddle.com/#!17/5d0e5/1试图构建这样的东西。Postgre 9.6

在最终表格中，您需要获得以下平均值： 在此处输入图像描述

sql

1 个回答

  1. Best Answer
    WITH cte AS ( select upload,
                     count(distinct user_id) as "count",
                     MIN(upload) OVER (PARTITION BY date_trunc('Week', upload)) min_upload
              from demo
              where upload between '2020-01-01' and '2020-05-01'
              group by upload )
SELECT "count",
       upload,
       CASE WHEN upload = min_upload 
            THEN AVG("count") OVER (PARTITION BY date_trunc('Week', upload))::CHAR(5)
            ELSE ''
            END "average(count)"
FROM cte
ORDER BY upload;

    小提琴

    • 1

相关问题