我正在使用 Oracle 并且我有一個包含 TIMESTAMP 型別列的表。我想知道如何從資料庫上過去 4 周的活動中提取記錄，按周磁區。
在第 1 周插入以下行
kc 2 04-10-2021
vc 3 06-10-2021
vk 4 07-10-2021
在第 2 周插入以下行
cv 1 12-10-2021
ck 5 14-10-2021
在第 3 周插入以下行
vv 7 19-10-2021
在第 4 周插入以下行
vx 7 29-10-2021
表現在有
SQL>select * from tab;
NAME VALUE TIMESTAMP
-------------------- ----------
kc 2 04-10-2021
vc 3 06-10-2021
vk 4 07-10-2021
cv 1 12-10-2021
ck 5 14-10-2021
vv 7 19-10-2021
vx 7 29-10-2021
我想要一個查詢，它可以告訴我過去 4 周內每周添加的行數。
這是我想看到的
numofrows week
--------- -----
3 1
2 2
1 3
1 4
uj5u.com熱心網友回復：
一種選擇是使用to_char函式及其iw引數：
SQL> with test (name, datum) as
2 (select 'kc', date '2021-10-04' from dual union all
3 select 'vc', date '2021-10-06' from dual union all
4 select 'vk', date '2021-10-07' from dual union all
5 select 'cv', date '2021-10-12' from dual union all
6 select 'ck', date '2021-10-14' from dual union all
7 select 'vv', date '2021-10-19' from dual union all
8 select 'vx', DATE '2021-10-29' from dual
9 )
10 select to_char(datum, 'iw') week,
11 count(*)
12 from test
13 where datum >= add_months(sysdate, -1) --> the last month
14 group by to_char(datum, 'iw');
WE COUNT(*)
-- ----------
42 1
43 1
40 3
41 2
SQL>
第 13 行：我故意使用“一個月”而不是“4 周”，因為我認為（可能是錯誤的）你實際上想要那個（你知道，“一個月有 4 周”——不完全是，但接近，有時不夠接近）。
如果你想要 4 周，那是什么？Sysdate減去28 天（因為每周有 7 天）？然后你將第 13 行修改為
where datum >= trunc(sysdate - 4*7)
或者，也許真的是過去 4 周：
SQL> with test (name, datum) as
2 (select 'kc', date '2021-10-04' from dual union all
3 select 'vc', date '2021-10-06' from dual union all
4 select 'vk', date '2021-10-07' from dual union all
5 select 'cv', date '2021-10-12' from dual union all
6 select 'ck', date '2021-10-14' from dual union all
7 select 'vv', date '2021-10-19' from dual union all
8 select 'vx', DATE '2021-10-29' from dual
9 ),
10 temp as
11 (select to_char(datum, 'iw') week,
12 count(*) cnt,
13 row_number() over (order by to_char(datum, 'iw') desc) rn
14 from test
15 group by to_char(datum, 'iw')
16 )
17 select week, cnt
18 from temp
19 where rn <= 4
20 order by week;
WE CNT
-- ----------
40 3
41 2
42 1
43 1
SQL>
現在您有多種選擇，看看哪一種最適合（如果有的話）。
我“模擬”了缺失的資料（參見TESTCTE），創建了一個日歷 ( calend) 并......完成了這項作業。閱讀代碼中的注釋：
SQL> with test (name, datum) as
2 -- sample data
3 (select 'vv', date '2021-10-19' from dual union all
4 select 'vx', DATE '2021-10-29' from dual
5 ),
6 calend as
7 -- the last 31 days; 4 weeks are included, obviously
8 (select max_datum - level 1 datum
9 from (select max(a.datum) max_datum from test a)
10 connect by level <= 31
11 ),
12 joined as
13 -- joined TEST and CALEND data
14 (select to_char(c.datum, 'iw') week,
15 t.name
16 from calend c left join test t on t.datum = c.datum
17 ),
18 last4 as
19 -- last 4 weeks
20 (select week, count(name) cnt,
21 row_number() over (order by week desc) rn
22 from joined
23 group by week
24 )
25 select week, cnt
26 from last4
27 where rn <= 4
28 order by week;
WE CNT
-- ----------
40 0
41 0
42 1
43 1
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/341338.html