sql - 选择具有滚动日期标准的数据

Question

以下查询返回给定月份和品牌的“成员(member)”的不同计数(见下图)。

select to_char(transaction_date, 'YYYY-MM') as month, brand,
   count(distinct UNIQUE_MEM_ID) as distinct_count
from source.table
group by  to_char(transaction_date, 'YYYY-MM'), brand;

数据是在月底后 15 天后收集的(这意味着 2016 年 9 月的月度数据在 10 月 15 日之前不会是 100%)。我只关心月度数据。

我要构建的查询:直到本月(10 月)15 日，上个月(9 月)的数据应该反射(reflect) 8 月的数据。当前部分月份(十月)应默认为前一个月，因此也符合上述逻辑。

在本月 15 日之后，上个月的数据(9 月)现在是 100%，因此 9 月应该反射(reflect) 9 月(10 月将反射(reflect) 9 月，直到 11 月 15 日，依此类推)。

当前部分月份将始终 = 前一个月。查询的复杂性在于如何计算前一个月。

此查询将滚动运行，因此需要是动态的。

明确地说，我正在尝试构建一个查询，其中前一个月(直到本月末 + 15 天)的 distinct_count 应该反射(reflect)(当前月 - 2)值(对于每个品牌)。当月结束后 15 天后，上月 =(当月 - 1)。

当月部分默认为上月数据。 15 天的值应该是可变的/可修改的。

Answer 1

首先，将查询简化为:

select to_char(transaction_date, 'YYYY-MM') as month, brand,
       count(distinct members) as distinct_count 
from source.table
group by members, to_char(transaction_date, 'YYYY-MM'), brand;

然后，你就会遇到问题。问题是一行(比如从 8 月 20 日开始)需要分成两组。一个简单的 group by 无法处理这个问题。所以，让我们使用union all。我认为结果是这样的:

select date_trunc('month', transaction_date) as month, brand,
       count(distinct members) as distinct_count 
from source.table
where (date_trunc('month', transaction_date) < date_trunc('month' current_date) - interval '1 month') or
      (day(current_date) > 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by date_trunc('month', transaction_date), brand
union all
select date_trunc('month' current_date) - interval '1 month' as month, brand,
       count(distinct members) as distinct_count 
from source.table
where (day(current_date) < 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by brand;