我正在使用 Oracle SQL Developer，我需要從一個非常大的資料庫中提取計數。我需要計算 10 個季度（和總計）中每一個（總計）5 個站點的訪問次數和唯一身份訪問者數量 - 導致 120 次計數。這個資料庫中的每一行代表一次訪問，每個訪問者都有一個唯一的visitor_ID。
目前，我每個計數都有一行，但我需要進行修改，這次我不想以如此低效的方式進行修改。
select
sum(case when visit_date between '01-JAN-2019 00:00:00' and '31-MAR-2019 00:00:00' and site=site1 then 1 else 0 end) as 19Q1_visits_site1,
count(distinct case when visit_date between '01-JAN-2019 00:00:00' and '31-MAR-2019 00:00:00' and site=site1 then visitor_id) as 19Q1_unique_site1,
[...]
from visitdata
where [additional qualifiers];
如果可能的話，我想創建一些類似的東西：
19Q1 = visit_date between '01-JAN-2019 00:00:00' and '31-MAR-2019 00:00:00'
19Q2 = visit_date between '01-APR-2019 00:00:00' and '30-JUN-2019 00:00:00'
[...]
allQ = visit_date between '01-JAN-2019 00:00:00' and '30-SEP-2021 00:00:00'
S1 = site in (site1)
[...]
allS = site in (site1, site2, site3, site4, site5)
sites = [S1, S2, S3, S4, S5, allS]
quarters = [19Q1, 19Q2, ..., allQ]
for s in sites:
for q in quarters:
select
sum(case when q and s then 1 else 0 end) as (str(q) 'visits' str(s)),
count(distinct case when q and s then visitor_id) as (str(q) 'unique' str(s))
from visitdata
where [additional qualifiers];
我知道 SQL 不會執行 for 回圈。任何建議都會很棒，因此我不必創建另一個包含近 200 行的令人尷尬的腳本。謝謝！
uj5u.com熱心網友回復：
非常簡單，使用內置或用戶定義的函式來獲取日期并回傳該日期所屬的季度。（SQL Server 支持“季度”作為日期部分，但如果 Oracle 不支持，您可以自己撰寫它。您也可以將visit_quarter作為物化計算列添加到您的表中，如果您經常使用它，甚至可以對其進行索引。 ) 然后您可以按照以下方式撰寫單個分組查詢
SELECT
site,
quarter(visit_date) as Q,
COUNT(visitor_id) as numvisits,
COUNT(DISTINCT visitor_id) AS numDistinctVisitors
FROM T
WHERE <additional conditions>
GROUP BY site, quarter(visit_date)
ORDER BY site, Q
uj5u.com熱心網友回復：
要查找季度，您可以在日期上使用 TO_CHAR()，并更改季度的開始日期和結束日期，您可以使用 ADD_MONTHS() 例如
桌子
create table randomdates( date_ )
as
select sysdate - dbms_random.value( 1, 600 )
from dual
connect by level <= 50 ;
詢問
select date_
, to_char( date_, 'YYYY-Q' ) quarter
from randomdates
;
-- result
DATE_ QUARTER
09/23/2021 2021-3
09/24/2020 2020-3
03/23/2020 2020-1
03/29/2021 2021-1
11/29/2020 2020-4
03/05/2021 2021-1
04/08/2021 2021-2
...
GROUP BY 也應該是可能的 - 正如@Steve Kass 所建議的那樣。
select to_char( date_, 'YYYY-Q' ), count(*)
from randomdates
group by to_char( date_, 'YYYY-Q' )
order by 1 desc
;
TO_CHAR(DATE_,'YYYY-Q') COUNT(*)
2021-4 6
2021-3 13
2021-2 22
2021-1 13
2020-4 20
2020-3 12
2020-2 10
2020-1 4
...
從你的評論：
無論如何，我正在使用修改后的財政年度。您知道當訪問日期存盤為 yyyymmdd 時我如何定義季度嗎？
如果您需要不同的開始/結束日期，ADD_MONTHS() 可以幫助例如以下查詢中的 modified_quarters 比“標準”季度晚一個月開始。關于日期：在 Oracle 中，它們包含世紀、世紀中的年份、月份、月份中的日期、小時、分鐘和秒（7 個位元組）。您可以只使用 TO_CHAR() 并使用“格式模型”（例如下面示例中的“Q”）選取您需要的任何組件（日期的）。
-- Query executed in APEX.
-- Column date_ : no formatting (compare the output to the same query in the dbfiddle).
select date_
, to_char( date_, 'YYYY-Q' ) quarter
, to_char( date_, 'YY' ) || 'Q' || to_char( date_, 'Q' ) quarter_
, to_char( add_months( date_, 1 ), 'YYYY-Q' ) modified_quarter
from randomdates
;
DATE_ QUARTER QUARTER_ MODIFIED_QUARTER
09/23/2021 2021-3 21Q3 2021-4
09/24/2020 2020-3 20Q3 2020-4
03/23/2020 2020-1 20Q1 2020-2
03/29/2021 2021-1 21Q1 2021-2
11/29/2020 2020-4 20Q4 2020-4
03/05/2021 2021-1 21Q1 2021-2
要計算每個站點的小計和總計（計數），您可以使用 GROUP BY ROLLUP() 例如
表格和資料
-- Caution: dates in this table are not the same as in the randomdates table.
create table sitesanddates( site_, date_ )
as
select
trunc( dbms_random.value( 1, 6 ) )
, sysdate - dbms_random.value( 1, 600 )
from dual
connect by level <= 50 ;
-- group by site and quarter
select site_, to_char( date_, 'YYYY-Q' ), count(*)
from sitesanddates
group by site_, to_char( date_, 'YYYY-Q' )
order by 1, 2
;
SITE_ TO_CHAR(DATE_,'YYYY-Q') COUNT(*)
1 2020-1 1
1 2020-4 3
1 2021-1 1
1 2021-2 1
1 2021-3 2
2 2020-1 1
2 2020-2 1
按匯總分組
select site_, to_char( date_, 'YYYY-Q' ) q_, count(*)
from sitesanddates
group by rollup( site_, to_char( date_, 'YYYY-Q' ) )
order by 1, 2
;
SITE_ Q_ COUNT(*)
1 2020-1 1
1 2020-4 3
1 2021-1 1
1 2021-2 1
1 2021-3 2
1 - 8 -- <- subtotal for site 1
2 2020-1 1
2 2020-2 1
...
5 2020-4 2
5 2021-1 2
5 2021-2 1
5 2021-4 1
5 - 10 -- <- subtotal for site 5
- - 50 -- <- grand total
鏈接到 dbfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346931.html