我需要在 Oracle 中撰寫查詢，如下面的螢屏截圖所示。任何幫助是極大的贊賞。提前非常感謝。瓦迪。
帶有樣本資料的表格：
CREATE TABLE fee_check (
trans_date DATE,
fee1 NUMBER(6,3),
fee2 NUMBER(6,3)
);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('18/04/2022','dd/mm/yyyy'), 0.74, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('19/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('20/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('21/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('22/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('23/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('24/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('25/04/2022','dd/mm/yyyy'), 0.76, 0.87);
COMMIT;
uj5u.com熱心網友回復：
這是您問題的解決方案
SELECT MIN(trans_date) trans_date, COUNT(*) DayCount, fee1, fee2
FROM fee_check
GROUP BY fee1,fee2
ORDER BY trans_date
uj5u.com熱心網友回復：
如果您只想計算連續行（而不是將具有相同費用值的非連續組聚合在一起），那么從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE逐行處理：
SELECT *
FROM fee_check
MATCH_RECOGNIZE(
ORDER BY trans_date
MEASURES
FIRST(trans_date) AS trans_date,
COUNT(trans_date) AS day_count,
FIRST(fee1) AS fee1,
FIRST(fee2) AS fee2
PATTERN (same_fees )
DEFINE same_fees AS fee1 = FIRST(fee1) AND fee2 = FIRST(fee2)
)
或者，在早期版本中，您可以使用分析函式：
SELECT MIN(trans_date) AS trans_date,
COUNT(*) AS day_count,
MIN(fee1) AS fee1,
MIN(fee2) AS fee2
FROM (
SELECT f.*,
ROW_NUMBER() OVER (ORDER BY trans_date) -
ROW_NUMBER() OVER (PARTITION BY fee1, fee2 ORDER BY trans_date) AS grp
FROM fee_check f
)
GROUP BY grp
其中，對于樣本資料：
CREATE TABLE fee_check (trans_date, fee1, fee2) AS
SELECT DATE '2022-04-18', 0.74, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-19', 0.75, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 0.75, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-22', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-23', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-24', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-25', 0.76, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-26', 0.75, 0.87 FROM DUAL;
注意：在末尾添加了一個額外的行，該行fee1與fee2資料集中較早的值相同。
兩個輸出：
TRANS_DATE DAY_COUNT 費用1 費用2 2022-04-18 00:00:00 1 .74 .87 2022-04-19 00:00:00 2 .75 .87 2022-04-21 00:00:00 4 .73 .87 2022-04-25 00:00:00 1 .76 .87 2022-04-26 00:00:00 1 .75 .87
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/467500.html
下一篇：PL/SQL表變異和觸發格式