如何在一年中的每个日期按年龄和状态对项目进行分组/计数？

pdsfdshx 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(380)

我正在尝试从多年数据集构建一个查询( tickets 表）的支持票，以及 ticked_id , status , created_on 日期和 closed_on 每张票的日期。还有一个通用的 dates 表i可以连接/查询到日期列表。
我想为今年创建一个“燃尽”图表，显示在今年任何给定日期至少一年的开放门票数量。我已经能够创建使用 sum(case... 声明按日期分组—例如，显示给定的一周创建了多少张票—但我无法计算如何按今年的每一天或每一周分组当日打开的至少一年的票的数量。
感谢您的帮助。
示例数据：

ticket_id | status | created_on | closed_on
--------------------------------------------
 1          open     1/5/2019     
 2          open     1/26/2019
 3          closed   1/28/2019    2/1/2020
 4          open     6/1/2019
 5          closed   6/5/2019     1/1/2020

我寻求的示例结果：

Date (2020)    |     Count of Year+ Aged Tickets
------------------------------------------------
1/1/2020             0
1/2/2020             0
1/3/2020             0
1/4/2020             0
1/5/2020             1
1/6/2020             1
... (skipping dates here but want all dates in results)...
1/25/2020            1
1/26/2020            2
1/27/2020            2
1/28/2020            3
1/29/2020            3
1/30/2020            3
1/31/2020            3
2/1/2020             2
... (skipping dates here but want all dates up to current date in results)...
``` `ticket_id 1` 在2020年1月5日达到一岁，并且仍然开放（仍在统计中） `ticket_id 2` 于2020年1月26日满一岁，仍在营业（仍在统计中） `ticket_id 3` 在2020年1月28日达到一岁，仍然开放，增加了计数，但在2020年2月1日关闭，减少了计数 `ticket_id 4` 只有在2020年6月1日仍然开放的情况下才会增加计数，但如果在此之前关闭，则不会增加计数 `ticket_id 5` 不会出现在计数中，因为它从未达到一岁并且是关闭的

sql Join Date amazon-redshift recursive-query

来源：https://stackoverflow.com/questions/62096111/sql-how-to-group-count-items-by-age-and-status-on-every-date-of-a-year

2条答案

按热度按时间

9cbw7uwe1#

一种方法是构建一个连续的日期列表，然后使用“左联接”和条件逻辑将表带到表中，最后进行聚合。
这将给出您想要的2020年的结果。

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + I * interval '1 day' dt 
    from generate_series(0, 365) i 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
group by d.dt

如果您的红移版本不支持 generate_series() ，可以用自定义数字表或 row_number() 靠着一张大table mylargetable ):

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + row_number() over(order by 1) * interval '1 day' dt 
    from mylargetable 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
where d.dt < date '2021-01-01'
group by d.dt

赞(0）回复(0）举报 2021-08-13

kulphzqa2#

如果票证id是唯一的，那么您可以这样做以获得至少1年前的所有票证

select ticket_id, created_on , status where status = 'open' and created_on <= dateadd(year,-1,getdate())

如果你想计算每月的票数

select count(ticket_id), month(created_on) , status where status = 'open' and created_on <= dateadd(year,-1,getdate())
group by month(created_on)

赞(0）回复(0）举报 2021-08-13

我来回答

如何在一年中的每个日期按年龄和状态对项目进行分组/计数？

2条答案

相关问题

热门标签

最新问答