考慮我有一張這樣的桌子
PASSENGER CITY DATE
43 NEW YORK 1-Jan-21
44 CHICAGO 4-Jan-21
43 NEW YORK 2-Jan-21
43 NEW YORK 3-Jan-21
44 ROME 5-Jan-21
43 LONDON 4-Jan-21
44 CHICAGO 6-Jan-21
44 CHICAGO 7-Jan-21
我如何按順序對“Passenger”和“City”列進行分組以獲得如下結果？
PASSENGER CITY COUNT
43 NEW YORK 3
44 CHICAGO 1
44 ROME 1
43 LONDON 1
44 CHICAGO 2
uj5u.com熱心網友回復：
從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE：
SELECT *
FROM table_name
MATCH_RECOGNIZE (
PARTITION BY passenger
ORDER BY "DATE"
MEASURES
FIRST(city) AS city,
COUNT(*) AS count
PATTERN (same_city )
DEFINE
same_city AS FIRST(city) = city
);
其中，對于樣本資料：
CREATE TABLE table_name (PASSENGER, CITY, "DATE") AS
SELECT 43, 'NEW YORK', DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 44, 'CHICAGO', DATE '2021-01-04' FROM DUAL UNION ALL
SELECT 43, 'NEW YORK', DATE '2021-01-02' FROM DUAL UNION ALL
SELECT 43, 'NEW YORK', DATE '2021-01-03' FROM DUAL UNION ALL
SELECT 44, 'ROME', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 43, 'LONDON', DATE '2021-01-04' FROM DUAL UNION ALL
SELECT 44, 'CHICAGO', DATE '2021-01-06' FROM DUAL UNION ALL
SELECT 44, 'CHICAGO', DATE '2021-01-07' FROM DUAL
輸出：
乘客 城市 數數 43 紐約 3 43 倫敦 1 44 芝加哥 1 44 羅馬 1 44 芝加哥 2
如果您對輸入結果集進行了排序（注意：表應該被認為是無序的）并希望保持順序，那么：
SELECT *
FROM (SELECT t.*, ROWNUM AS rn FROM table_name t)
MATCH_RECOGNIZE (
PARTITION BY passenger
ORDER BY RN
MEASURES
FIRST(rn) AS rn,
FIRST("DATE") AS "DATE",
FIRST(city) AS city,
COUNT(*) AS count
PATTERN (same_city )
DEFINE
same_city AS FIRST(city) = city
)
ORDER BY rn
輸出：
乘客 注冊護士 日期 城市 數數 43 1 01-JAN-21 紐約 3 44 2 04-JAN-21 芝加哥 1 44 5 05-JAN-21 羅馬 1 43 6 04-JAN-21 倫敦 1 44 7 06-JAN-21 芝加哥 2
db<>在這里擺弄
uj5u.com熱心網友回復：
處理此類間隙和孤島問題的一種方法是計算間隙的排名。
然后分組也在那個排名上。
SELECT PASSENGER, CITY
, COUNT(*) AS "Count"
-- , MIN("DATE") AS StartDate
-- , MAX("DATE") AS EndDate
FROM (
SELECT q1.*
, SUM(gap) OVER (PARTITION BY PASSENGER ORDER BY "DATE") as Rnk
FROM (
SELECT PASSENGER, CITY, "DATE"
, CASE
WHEN 1 = TRUNC("DATE")
- TRUNC(LAG("DATE")
OVER (PARTITION BY PASSENGER, CITY ORDER BY "DATE"))
THEN 0 ELSE 1 END as gap
FROM table_name t
) q1
) q2
GROUP BY PASSENGER, CITY, Rnk
ORDER BY MIN("DATE"), PASSENGER
| 乘客 | 城市 | 數數 |
|---|---|---|
| 43 | 紐約 | 3 |
| 43 | 倫敦 | 1 |
| 44 | 芝加哥 | 1 |
| 44 | 羅馬 | 1 |
| 44 | 芝加哥 | 2 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367516.html
上一篇：創建SQL查詢時遇到問題-初學者
下一篇：合并SQL中的幾行