Oracle
Oracle 將行計數分組到列
我有一個包含這些數據的表:
- ID - PRIMARY KEY, INTEGER - PC_ID - INTEGER - USER_ID - INTEGER - PAYMENT_DATETIME - TIMESTAMP - FIO - VARCHAR2(255)
當我這樣做時,
SELECT
我得到了這個(範例):1, 1, 1, 08.06.2018 17:33:26, Test 2, 1, 1, 08.06.2018 17:34:52, Test 3, 1, 1, 08.06.2018 18:01:33, Test 4, 1, 1, 08.06.2018 18:03:17, Test2 5, 1, 1, 08.06.2018 19:26:41, Test 6, 1, 1, 09.06.2018 13:22:58, Test2
因此,如果我將它們按
PC_ID
,USER_ID
, DistinctFIO
,DATE
(DD. MM. YYYY),TIME
(HH24) 分組為行,以顯示FIO
每台 pc 的每個使用者每天 07 到 22 小時的不同數量,我得到:1, 1, 08.06.2018, 17, 1 1, 1, 08.06.2018, 18, 2 1, 1, 08.06.2018, 19, 1 1, 1, 09.06.2018, 13, 1
最後一列是 distinct 的計數
FIO
。現在我想將其轉換為:
列:
PC_ID
,USER_ID
,DATE
(DD. MM. YYYY),TIME
(HH24) 用於 07 到 22 和TOTAL FOR DATE
如果小時計數為 0,則顯示 0:
所以結構應該是這樣的:
PC_ID, USER_ID, DATE, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, TOTAL_FIO
我在考慮 CTE,可以使用它們來實現嗎?
可以使用 CTE,但 PIVOT 更容易實現。
如果對於 total_fio 您仍然想要不同,這將起作用:
SELECT * FROM (SELECT pc_id, user_id, to_char(payment_datetime,'DD.MM.YYYY') AS "date", To_char(payment_datetime, 'HH24') AS "hour", fio, count(DISTINCT fio) OVER ( PARTITION BY to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio FROM foobar WHERE To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' ) PIVOT (Count(DISTINCT fio) FOR "hour" IN ('07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22'));
或者,如果您想要所有
hour
列的總和,我不確定在不進入 PL/SQL 的情況下是否有一種優雅的方法:SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12" + "13" + "14" + "15" + "16" + "17" + "18" + "19" + "20" + "21" + "22" AS total_fio FROM (SELECT pc_id, user_id, to_char(payment_datetime,'DD.MM.YYYY') AS date_day, To_char(payment_datetime, 'HH24') AS date_hour, fio FROM foobar WHERE To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22') PIVOT (Count(DISTINCT fio) FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11", '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16", '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21", '22' AS "22")) x;
我是新來的,所以沒有足夠的代表來評論你的實現,但它肯定是一個可靠的嘗試。我看不出你會得到不一致的結果有什麼問題。至於優化,`PIVOT’ 會為您進行分組。因此,通過使用帶有附加 group by 的 CTE 開始,查詢必須完成的工作幾乎翻了一番。我上面的第二個查詢本質上是您的優化版本(它們非常相似)。
在回答我的問題之前,我閱讀了一些關於 Pivot 子句的內容並進行了以下查詢:
WITH PIVOT_DATA AS ( SELECT PC_ID, USER_ID, TO_DATE(TO_CHAR(PAYMENT_DATETIME,'DD.MM.YYYY'), 'DD.MM.YYYY HH24:MI:SS') as THEDATE, FIO, EXTRACT(HOUR FROM PAYMENT_DATETIME) AS THEHOUR FROM PAYMENTS GROUP BY PC_ID, USER_ID, TO_DATE(TO_CHAR(PAYMENT_DATETIME,'DD.MM.YYYY'), 'DD.MM.YYYY HH24:MI:SS'), EXTRACT(HOUR FROM PAYMENT_DATETIME), FIO ) SELECT CLIENTS.*, CLIENTS.H7+CLIENTS.H8+CLIENTS.H9 +CLIENTS.H10+CLIENTS.H11+CLIENTS.H12 +CLIENTS.H13+CLIENTS.H14+CLIENTS.H15 +CLIENTS.H16+CLIENTS.H17+CLIENTS.H18 +CLIENTS.H19+CLIENTS.H20+CLIENTS.H21+CLIENTS.H22 AS THETOTAL FROM PIVOT_DATA PIVOT ( COUNT(DISTINCT FIO) FOR THEHOUR IN (7 H7,8 H8,9 H9,10 H10,11 H11,12 H12,13 H13,14 H14,15 H15,16 H16,17 H17,18 H18,19 H19,20 H20,21 H21,22 H22) ) CLIENTS;
沒關係,還是有什麼嚴重的問題?它可以工作並給出我需要的結果,但它相當大,我懷疑它是否經過優化。