我正在嘗試從 Oracle 資料庫表中收集資料GROUP BY。我認為我需要兩個級別GROUP BY，但我不知道如何完成我的查詢。
通過這種方式，我有一個包含數百萬狀態的 STATUS 表：
REQUEST STATUS
------- -----------
ID -> REQUEST_ID
... ID
STATUS_CODE
....
請求流程的示例（STATUS 表）：
SELECT ... FROM STATUS WHERE REQUEST_ID = 1 ORDER BY ID;
ID REQUEST_ID STATUS_CODE STATUS_ALIAS CREATED
1 1 201 REQUEST_SAVED
2 1 204 REQUEST_SIGNATURE_VALID
3 1 210 REQUEST_XML_VALID
4 1 280 REQUEST_ACCEPTED
5 1 310 SENT_TO_SYSTEM_1_FOR_VERIFICATION
6 1 320 SENT_TO_SYSTEM_2_FOR_VERIFICATION
7 1 521 SYSTEM_1_VERIFICATION_ERROR
8 1 511 SYSTEM_2_VERIFICATION_ERROR
24880 1 310 SENT_TO_SYSTEM_1_FOR_VERIFICATION
24881 1 320 SENT_TO_SYSTEM_2_FOR_VERIFICATION
24885 1 620 SYSTEM_1_VERIFICATION_TIMEOUT
24886 1 610 SYSTEM_2_VERIFICATION_TIMEOUT
24887 1 310 SENT_TO_SYSTEM_1_FOR_VERIFICATION
24888 1 320 SENT_TO_SYSTEM_2_FOR_VERIFICATION
.....
我想收集REQUEST_ID處于 VERIFICATION 狀態但尚未 TIMEOUTED 的 s，如下所示：
24887 1 310 SENT_TO_SYSTEM_1_FOR_VERIFICATION
.....
這就是我選擇該資料的方式：
SELECT REQUEST_ID, STATUS_CODE, MAX(ID) FROM STATUS
GROUP BY REQUEST_ID, STATUS_CODE HAVING STATUS_CODE = 310;
REQUEST_ID STATUS_CODE MAX(ID)
1 310 24887
這正確顯示了ID我需要從哪里過濾分組的 STATUS 記錄REQUEST_ID，但是當我將此查詢與外部組合SELECT以顯示REQUEST_IDs 時，它不起作用。
這是我迄今為止最好的嘗試：
SELECT T1.REQUEST_ID FROM STATUS T1
GROUP BY T1.REQUEST_ID, T1.ID HAVING T1.ID >= (
SELECT MAX(ID) FROM STATUS T2
GROUP BY T2.REQUEST_ID, T2.STATUS_CODE
HAVING T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620)
);
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
更新
建議解決方案的問題如下。讓我們假設流程以這種方式繼續：
24887 1 310 SENT_TO_SYSTEM_1_FOR_VERIFICATION
24888 1 320 SENT_TO_SYSTEM_2_FOR_VERIFICATION
24889 1 460 SYSTEM_2_VERIFICATION_OK
24890 1 510 SYSTEM_1_VERIFICATION_ERROR
then if there is no other responses from system 1 within let's say 10 mins, I need to add a timeout for only system 1:
24891 1 620 SYSTEM_1_VERIFICATION_TIMEOUT
But only one time. That is why the query must filter out 620. Othervise this request-id 1 appears in the result set again despite the timeout flag was set earlier in the previous check run.
UPDATE 2
WHERE我可以在 Java 級別撰寫一個適當的“ ”條件并找到lambda filters處于“卡住”狀態的請求，我需要在其中添加超時狀態。但這樣我總是需要從 Java 回圈遍歷整個 STATUS 表并在每個GRUOP BY REQUEST_ID組上執行我的 java 邏輯。這是不好的和耗時的，會運行很長時間，所以這個解決方案將無法正常作業。也許我需要一個存盤程序？這就是為什么我想要一個“超級”SQL 查詢，它回傳卡住的請求的 ID，我可以為具有這些 ID 的請求設定超時標志。
uj5u.com熱心網友回復：
我可能會感到困惑，但我認為您需要的只是：
SELECT REQUEST_ID, STATUS_CODE, MAX(ID)
FROM STATUS
WHERE STATUS_CODE IN (310, 320)
GROUP BY REQUEST_ID, STATUS_CODE;
這T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620)是沒有意義的，因為當您將狀態代碼指定為 310/320 時，它肯定不會在 610/620 中。
uj5u.com熱心網友回復：
在HAVING T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620)第二個子句中沒有添加任何內容，就好像它在 (310,320) 中它不能在 (610,620) 中一樣。有關架構、測驗和其他查詢，請參閱下面的 dbFiddle 鏈接。
SELECT REQUEST_ID, STATUS_CODE, MAX(ID) AS MAX_ID FROM STATUS WHERE STATUS_CODE IN (310, 320) GROUP BY REQUEST_ID, STATUS_CODE;請求 ID | STATUS_CODE | MAX_ID ---------: | ----------: | -----： 1 | 310 | 24887 1 | 320 | 24888
db<>在這里擺弄
uj5u.com熱心網友回復：
在 Oracle 中，您可以使用LAST聚合函式 inHAVING子句按請求的最終狀態進行過濾。
在所有 DBMS 中，您可以row_number()用來標記最后一行，然后對其進行過濾。
假設ID列始終遞增（或將其替換為始終遞增的列），您將得到：
create table t (ID, REQUEST_ID, STATUS_CODE, STATUS_ALIAS) as select 1, 1, 201, 'REQUEST_SAVED' from dual union all select 2, 1, 204, 'REQUEST_SIGNATURE_VALID' from dual union all select 3, 1, 210, 'REQUEST_XML_VALID' from dual union all select 4, 1, 280, 'REQUEST_ACCEPTED' from dual union all select 5, 1, 310, 'SENT_TO_SYSTEM_1_FOR_VERIFICATION' from dual union all select 6, 1, 320, 'SENT_TO_SYSTEM_2_FOR_VERIFICATION' from dual union all select 7, 1, 521, 'SYSTEM_1_VERIFICATION_ERROR' from dual union all select 8, 1, 511, 'SYSTEM_2_VERIFICATION_ERROR' from dual union all select 24880, 1, 310, 'SENT_TO_SYSTEM_1_FOR_VERIFICATION' from dual union all select 24881, 1, 320, 'SENT_TO_SYSTEM_2_FOR_VERIFICATION' from dual union all select 24885, 1, 620, 'SYSTEM_1_VERIFICATION_TIMEOUT' from dual union all select 24886, 1, 610, 'SYSTEM_2_VERIFICATION_TIMEOUT' from dual union all select 24887, 1, 310, 'SENT_TO_SYSTEM_1_FOR_VERIFICATION' from dual union all select 24888, 1, 320, 'SENT_TO_SYSTEM_2_FOR_VERIFICATION' from dual union all select 30000, 2, 201, 'REQUEST_SAVED' from dual union all select 30001, 2, 204, 'REQUEST_SIGNATURE_VALID' from dual union all select 30002, 2, 210, 'REQUEST_XML_VALID' from dual union all select 30003, 2, 280, 'REQUEST_ACCEPTED' from dual union all select 30004, 2, 310, 'SENT_TO_SYSTEM_1_FOR_VERIFICATION' from dual union all select 30005, 2, 320, 'SENT_TO_SYSTEM_2_FOR_VERIFICATION' from dual union all select 30006, 2, 521, 'SYSTEM_1_VERIFICATION_ERROR' from dual
select request_id , max(status_alias) keep(dense_rank last order by id asc) as final_status from t /*To restrict input as much as possible*/ where status_code >= 310 group by request_id having max(status_code) keep(dense_rank last order by id asc) in (310, 320)請求 ID | 最終狀態 ---------: | :-------------------------------- 1 | SENT_TO_SYSTEM_2_FOR_VERIFICATION
with a as ( select t.* , row_number() over( partition by request_id order by id desc ) as rn from t where status_code >= 310 ) select * from a where rn = 1 and status_code in (310, 320)身份證 | 請求 ID | STATUS_CODE | 狀態別名 | 注冊護士 ----: | ---------: | ----------: | :-------------------------------- | -： 24888 | 1 | 320 | SENT_TO_SYSTEM_2_FOR_VERIFICATION | 1
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448758.html