應用GROUPBY后檢查相同的數量（棘手的場景）-有解無憂

我正在嘗試檢查特定帳號和不同組織的輸出是否相同并顯示 Oracle 中的差異

想象一下，我有一個名為myTable的表，它包含 2 個欄位： accountnumber和org_id

僅存在 3 個org_id：81、281 和 404

應用 GROUP BY 后檢查相同的數量（棘手的場景）

資料集：

|accountnumber|org_id|
|4435354      |81    |
|4435354      |281   |
|4435354      |404   |
|3333333      |81    |
|3333333      |281   |
|4444444      |81    |
|4444444      |81    |
|4444444      |281   |
|4444444      |404   |

我想找到每個 org_id 沒有確切金額的所有不同帳戶

例如：

帳戶 4435354 有 1 行 org_id 81、1 行 org_281 和 1 行 org_id 404，所以在這種情況下是正確的

帳戶 3333333 有 1 行用于 org_id 81，1 行用于 org_281，而 404 沒有，因此存在差異

賬戶 4444444 有 2 行 org_id 81，1 行 org_281 和 1 行 org_id 404，所以存在差異

期望的輸出：

ACCOUNTNUMBER| ORG_ID|COUNT(*)
|3333333      |  81   |1
|3333333      |  281  |1
|3333333      |  404  |0
|4444444      |  81   |2
|4444444      |  281  |1
|4444444      |  404  |1

我怎樣才能在 Oracle 中實作類似的目標？

編輯#1

此方案有效，不應顯示

|9999999      |81    |
|9999999      |81    |
|9999999      |81    |
|9999999      |404   |
|9999999      |404   |
|9999999      |404   |
|9999999      |281   |
|9999999      |281   |
|9999999      |281   |

這是正確的，因為該帳號的每個 org_id 都有 3 個，并且是正確的

uj5u.com熱心網友回復：

您已經在這里問了幾乎相同的問題：Find the discrepancies in sql (tricky situation) (而且您還沒有在那里選擇正確的答案)

只需添加count(*)或count(distinct org_id)：DBFiddle

select
  accountnumber
  ,count(org_id) cnt
  ,count(distinct org_id) cnt_distinct
  ,listagg(org_id,',')within group(order by org_id) orgs
  ,listagg(distinct org_id,',')within group(order by org_id) orgs_distinct
from mytable
group by accountnumber;

結果：

ACCOUNTNUMBER        CNT CNT_DISTINCT ORGS          ORGS_DISTINCT
------------- ---------- ------------ ------------- --------------
      3333333          2            2 81,281        81,281
      4435354          3            3 81,281,404    81,281,404
      4444444          4            3 81,81,281,404 81,281,404

如果你真的需要單獨的所有行：DBFiddle

select
  accountnumber
  ,org_id
  ,count(org_id)          over(partition by accountnumber) cnt
  ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
  ,listagg(org_id,',')within group(order by org_id)
     over(partition by accountnumber)
       as orgs
  ,listagg(distinct org_id,',')within group(order by org_id) 
     over(partition by accountnumber)
       as orgs_distinct
from mytable;

結果：

ACCOUNTNUMBER     ORG_ID        CNT CNT_DISTINCT ORGS          ORGS_DISTINCT
------------- ---------- ---------- ------------ ------------- --------------
      3333333         81          2            2 81,281        81,281
      3333333        281          2            2 81,281        81,281
      4435354         81          3            3 81,281,404    81,281,404
      4435354        281          3            3 81,281,404    81,281,404
      4435354        404          3            3 81,281,404    81,281,404
      4444444         81          4            3 81,81,281,404 81,281,404
      4444444         81          4            3 81,81,281,404 81,281,404
      4444444        281          4            3 81,81,281,404 81,281,404
      4444444        404          4            3 81,81,281,404 81,281,404

Listagg 檔案：

對于指定的度量，LISTAGG 對 ORDER BY 子句中指定的每個組中的資料進行排序，然后連接度量列的值。

更新：您可以添加一個謂詞來過濾您不想輸出的行： DBFiddle 3

select *
from (
    select
      accountnumber
      ,org_id
      ,count(org_id)          over(partition by accountnumber) cnt
      ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
      ,listagg(org_id,',')within group(order by org_id)
         over(partition by accountnumber)
           as orgs
      ,listagg(distinct org_id,',')within group(order by org_id) 
         over(partition by accountnumber)
           as orgs_distinct
    from mytable) v
where cnt<>3;

結果：

ACCOUNTNUMBER     ORG_ID        CNT CNT_DISTINCT ORGS          ORGS_DISTINCT
------------- ---------- ---------- ------------ ------------- --------------
      3333333         81          2            2 81,281        81,281
      3333333        281          2            2 81,281        81,281
      4444444         81          4            3 81,81,281,404 81,281,404
      4444444         81          4            3 81,81,281,404 81,281,404
      4444444        281          4            3 81,81,281,404 81,281,404
      4444444        404          4            3 81,81,281,404 81,281,404

uj5u.com熱心網友回復：

對于更新的問題：DBFiddle

select *
from (
    select 
        v.*
        ,min(cnt_org)over(partition by accountnumber) min_cnt_org
        ,max(cnt_org)over(partition by accountnumber) max_cnt_org
    from (
        select
          accountnumber
          ,org_id
          ,count(org_id)          over(partition by accountnumber) cnt
          ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
          ,count(*)               over(partition by accountnumber,org_id) cnt_org
          ,listagg(org_id,',')within group(order by org_id)
             over(partition by accountnumber)
               as orgs
          ,listagg(distinct org_id,',')within group(order by org_id) 
             over(partition by accountnumber)
               as orgs_distinct
        from mytable
        ) v
    ) v2
where cnt_distinct<>3
or min_cnt_org!=max_cnt_org;

結果：

ACCOUNTNUMBER     ORG_ID        CNT CNT_DISTINCT    CNT_ORG ORGS          ORGS_DISTINCT  MIN_CNT_ORG MAX_CNT_ORG
------------- ---------- ---------- ------------ ---------- ------------- -------------- ----------- -----------
      3333333         81          2            2          1 81,281        81,281                   1           1
      3333333        281          2            2          1 81,281        81,281                   1           1
      4444444         81          4            3          2 81,81,281,404 81,281,404               1           2
      4444444         81          4            3          2 81,81,281,404 81,281,404               1           2
      4444444        281          4            3          1 81,81,281,404 81,281,404               1           2
      4444444        404          4            3          1 81,81,281,404 81,281,404               1           2

uj5u.com熱心網友回復：

您可以使用：

WITH expected_orgs (org_id) AS (
  SELECT  81 FROM DUAL UNION ALL
  SELECT 281 FROM DUAL UNION ALL
  SELECT 404 FROM DUAL
)
SELECT accountnumber,
       org_id,
       cnt
FROM   (
  SELECT t.accountnumber,
         e.org_id,
         COUNT(t.org_id) AS cnt,
         MIN(COUNT(t.org_id)) OVER (PARTITION BY t.accountnumber) AS min_cnt,
         MAX(COUNT(t.org_id)) OVER (PARTITION BY t.accountnumber) AS max_cnt
  FROM   expected_orgs e
         LEFT OUTER JOIN table_name t
         PARTITION BY (t.accountnumber)
         ON (e.org_id = t.org_id)
  GROUP BY
         t.accountnumber,
         e.org_id
)
WHERE  min_cnt != max_cnt;

其中，對于樣本資料：

CREATE TABLE table_name (accountnumber, org_id) AS
SELECT 4435354,  81 FROM DUAL UNION ALL
SELECT 4435354, 281 FROM DUAL UNION ALL
SELECT 4435354, 404 FROM DUAL UNION ALL
SELECT 3333333,  81 FROM DUAL UNION ALL
SELECT 3333333, 281 FROM DUAL UNION ALL
SELECT 4444444,  81 FROM DUAL UNION ALL
SELECT 4444444,  81 FROM DUAL UNION ALL
SELECT 4444444, 281 FROM DUAL UNION ALL
SELECT 4444444, 404 FROM DUAL UNION ALL
SELECT 9999999,  81 FROM DUAL UNION ALL
SELECT 9999999,  81 FROM DUAL UNION ALL
SELECT 9999999,  81 FROM DUAL UNION ALL
SELECT 9999999, 281 FROM DUAL UNION ALL
SELECT 9999999, 281 FROM DUAL UNION ALL
SELECT 9999999, 281 FROM DUAL UNION ALL
SELECT 9999999, 404 FROM DUAL UNION ALL
SELECT 9999999, 404 FROM DUAL UNION ALL
SELECT 9999999, 404 FROM DUAL;

輸出：

帳號 ORG_ID 碳納米管

3333333 81 1

3333333 281 1

3333333 404 0

4444444 81 2

4444444 281 1

4444444 404 1

帳號	ORG_ID	碳納米管
3333333	81	1
3333333	281	1
3333333	404	0
4444444	81	2
4444444	281	1
4444444	404	1

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432938.html

標籤：sql 甲骨文

上一篇：避免此函式錯誤不允許DISTINCT選項(Oracle11g)

下一篇：如何制作成陣列并計數