如何根据条件多次聚合一条记录

Question

下表中有一堆记录。

product_id       produced_date    expired_date
123              2010-02-01       2012-05-31
234              2013-03-01       2014-08-04
345              2012-05-01       2018-02-25
...              ...             ...

我希望输出显示我们目前每月有多少未过期的产品。(比如说，如果产品在8月4日到期，我们仍然将其计入8月库存(

Month             n_products
2010-02-01        10
2010-03-01        12
...
2022-07-01        25
2022-08-01        15

我应该如何在Presto或Hive中做到这一点？非常感谢。

Answer 1

您可以使用以下SQL
这里我们使用case when来检查产品是否过期(produced_date >= expired_date(，如果它过期了，我们将其相加以获得已过期产品的计数。然后将到期月份的数据分组。

select 
TRUNC(expired_date, 'MM') expired_month,
SUM( case when produced_date >= expired_date then 1 else 0 end) n_products
from mytable
group by 1

Answer 2

我们可以使用unest和sequence函数来创建派生表；将我们的表与这个派生表连接起来，应该会给我们带来所需的结果。

Select m.month,count(product_id) as n_products
(Select 
(select x
from unnest(sequence(Min(month(produced_date)), Max(month(expired_date)), Interval '1' month)) t(x)
) as month
from table) m
left join table t on m.month >= t.produced_date and m.month <= t.expired_date

group by 1 
order by 1