Oracle DB-单查询非线性时间分组报告（1天，1周，1个月，3个月总计）

debugcn 发表于 Dev

理查德

我正在尝试构建一个报告，该报告本质上将是一个合并了这些查询输出的单个查询：

select count(*) from my_table where createddate >= CURRENT_DATE - 1;
select count(*) from my_table where createddate >= CURRENT_DATE - 7;
select count(*) from my_table where createddate >= CURRENT_DATE - 30;
select count(*) from my_table where createddate >= CURRENT_DATE - 90;
select count(*) from my_table;

这样输出将类似于：

Time_Period   Count
===================
Yesterday        5
Last Week       20
Last Month      50
Last 90 Days   100
Total         5000

我已经成功构建了几个线性时间序列查询（按天，按周，按月等）。但是，能够构建非线性报告查询并不算运气。

戈登·利诺夫

您可以轻松地将结果放在单独的列中：

select sum(case when createddate >= CURRENT_DATE - 1 then 1 else 0 end) as yesterday,
       sum(case when createddate >= CURRENT_DATE - 7 then 1 else 0 end) as last_week,
       sum(case when createddate >= CURRENT_DATE - 30 then 1 else 0 end) as last_month,
       sum(case when createddate >= CURRENT_DATE - 90 then 1 else 0 end) as last_90days,
       count(*) as total
from my_table;

如果您想要单独的行，则可以取消上述内容，或者只使用union all：

select 'Yesterday' as which, count(*) from my_table where createddate >= CURRENT_DATE - 1
union all
select 'Last week', count(*) from my_table where createddate >= CURRENT_DATE - 7
union all
select 'Last month', count(*) from my_table where createddate >= CURRENT_DATE - 30
union all
select 'Last 90 days', count(*) from my_table where createddate >= CURRENT_DATE - 90
union all
select 'Total', count(*) from my_table;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。