时间戳

首页 > 解决方案 > 具有给定开始日期和结束日期的月份中的天数

snowflake-cloud-data-platform - 具有给定开始日期和结束日期的月份中的天数

问题描述

我有一个表格片段如下：

在此处输入图像描述

有人可以帮我解决给我报告的查询，如下所示：

在此处输入图像描述

标签： snowflake-cloud-data-platform

解决方案

如果您有这样的日历维度，那将非常有帮助。例如，您可以使用以下内容创建一个：

CREATE TEMP TABLE calendar AS
SELECT dateadd(day,seq4(),'2017-01-01'::date) as cal_date, 
       date_trunc(month,cal_date) as cal_month_start, 
       dateadd(day,-1,(dateadd(month,1, cal_month_start))) as cal_month_end
FROM table(generator(rowcount => 10000));

使用此calendar表，您可以使用开始日期和结束日期加入它，并在该日期聚合以获得结果。使用 CTE 复制数据：

WITH x AS (
    SELECT member_id, start_date, end_date
    FROM (VALUES ('461043068_02','2018-08-07'::date,'2018-08-17'::date),
                 ('461043068_01','2019-05-28'::date,'2019-06-28'::date)
         ) y (member_id, start_date, end_date)
  )

现在，您可以像这样查询x和加入calendar：

SELECT member_id, cal_month_start, count(*)
FROM x
JOIN calendar c
  ON c.cal_date between dateadd(day,1,x.start_date) and x.end_date
GROUP BY 1,2;

这将为您提供您正在寻找的结果。请注意需要将 1 添加到start_date，这样您就不会计算日期范围的“边缘”两次。

此外，我没有cal_month_start在我的查询中格式化，但如果需要，您可以使用TO_VARCHAR()函数来执行此操作。

推荐阅读