我們有一個包含資料的表，其中一個日期列指示資料的日期（“planning_day”）和另一列用于記錄發送資料的時間（“first_sent_time”）。
我正在嘗試制作一份報告，顯示過去/未來我們在哪一天發送了資料。所以如果今天我們昨天發送了 2 個資料，今天發送了 5 個資料，后天發送了 1 個資料，結果應該是這樣的：
sent_day minus2 minus1 sameDay plus1 plus2
2021-11-24 0 2 5 0 1
...
我知道我可以在 postgres 中使用“過濾器”進行查詢：
select
trunc(t.first_sent_time),
count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = -2) as "minus2",
count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = -1) as "minus1",
count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 0) as "sameDay",
count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 1) as "plus1",
count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 2) as "plus2"
from
my_table t
group by
trunc(t.first_sent_time)
;
不幸的是，這個“過濾器”在 Oracle 中不存在。我在這里需要幫助。我嘗試了以下內容：
select
sent_day,
sum(minus2),
sum(minus1),
sum(sameDay),
sum(plus1),
sum(plus2)
from (
select
*
from (
select
b.id,
trunc(b.first_sent_time) as sent_day,
b.planning_day,
b.planning_day - trunc(b.first_sent_time) as day_diff
from
my_table b
where
b.first_sent_time >= DATE '2021-11-01'
)
pivot (
count(id) for day_diff in (-2 as "minus2",-1 as "minus1",0 as "sameDay", 1 as "plus1",2 as "plus2")
)
)
group by
sent_day
order by
sent_day
;
但它不起作用，感覺我太復雜了，必須有一個更簡單的解決方案。
uj5u.com熱心網友回復：
使用CASE聚合函式中的運算式來模擬filter.
這是一個簡化的例子
with dt as (
select 1 id , 1 diff_days from dual union all
select 2 id , 1 diff_days from dual union all
select 3 id , -1 diff_days from dual union all
select 4 id , -1 diff_days from dual union all
select 4 id , -1 diff_days from dual)
/* query */
select
count(case when diff_days = 1 then id end) as cnt_1,
count(case when diff_days = -1 then id end) as cnt_minus_1
from dt;
結果是
CNT_1 CNT_MINUS_1
---------- -----------
2 3
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/367870.html
上一篇：基于多個LOV限制LOV