时间戳

首页 > 解决方案 > SQL 窗口函数以每日级别计算不同的月份日期

sql - SQL 窗口函数以每日级别计算不同的月份日期

问题描述

我正在尝试获取一个查询，该查询将为我提供每天的 customerids 计数，但对于当前月份来说是唯一的（例如，如果 customerid 出现在当月的 1 日，我不想在 21 日再次计算它）。

我希望输出类似于

日期 每月独立客户
2021-04-29 500
2021-04-30 523
2021-05-01 45
2021-05-02 55
2021-05-03 72
... ...

所以我有

SELECT DATE_TRUNC('day', date) AS day,
       DATE_TRUNC('month', date) AS month,
       COUNT(DISTINCT customerid)
FROM table
GROUP BY 1, 2

但我基本上不知道我需要什么窗口函数（或任何其他方法）才能获得这个输出？

标签： sql

解决方案

好吧，如果你想要一个额外的列，那么你需要两个窗口函数。这个想法是计算用户在当月第一次出现的时间。

您的代码看起来像 Postgres，所以我将使用该语法：

SELECT DATE_TRUNC('day', date) AS day,
       DATE_TRUNC('month', date) AS month,
       COUNT(DISTINCT customerid) as customers_in_day,
       SUM(COUNT(*) FILTER (WHERE seqnum = 1)) OVER (ORDER BY DATE_TRUNC('day', date)) as customers_in_month
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY customerid, DATE_TRUNC('day', date) ORDER BY day) as seqnum
      FROM table t
     ) t
GROUP BY 1, 2

推荐阅读