帶有條件SQLOracle的COUNTDISTINCT WINDOWFUNCTION

我有一個資料表如下：

我試圖為Contract_id每個Value_date唯一的CustomerID.

期望的結果是：

Could anyone suggest how I could count distinct in this case?

I tried the window functions:

select distinct CustomerID,
Value_date,
count(distinct Contract_id) over (partition by CustomerID order by Value_date rows unbounded preceding) count_contract
from tbl_cus_contract;

但它不適用于錯誤：

錯誤報告 - SQL 錯誤：ORA-30487: ORDER BY not allowed here 30487. 00000 - “ORDER BY not allowed here” *原因：DISTINCT 函式和 RATIO_TO_REPORT 不能有 ORDER BY *操作：

謝謝大家的回答！！！我找到了一種更好的方法來計算不同，因為相同 CustomerID 的幾個不同 Contract_id 具有相同的 value_date。

我正在使用rank()

這是我的解決方案： https ://dbfiddle.uk/?rdbms=oracle_21&fiddle=c145c0432e003c1be52dbe0685c8d259

uj5u.com熱心網友回復：

我們可以嘗試使用ROW_NUMBER視窗函式并減去 1

SELECT t1.CustomerID,
       t1.Value_date,
       ROW_NUMBER() OVER(PARTITION BY t1.CustomerID ORDER BY t1.Value_date) - 1
FROM tbl_cus_contract t1

uj5u.com熱心網友回復：

您的預期結果似乎不需要計算不同的contract_id，如果這是真的，那么您可以省略在計數中使用 distinct 并且您的查詢會起作用。

SELECT t1.CustomerID
    , t1.Value_date
    , COUNT(*) OVER (PARTITION BY t1.CustomerID ORDER BY t1.Value_date) - 1
FROM tbl_cus_contract t1
ORDER BY t1.CustomerID
    , t1.Value_date

uj5u.com熱心網友回復：

您可以檢查contract_id給定客戶的第一次出現。然后只計算第一次出現。

WITH cte
     AS (SELECT customerid,value_date,contract_id,
                CASE WHEN Row_number() OVER( PARTITION BY customerid, contract_id
                           ORDER BY value_date ) = 1 THEN 1
                END AS first_occurrence_of_contract
         FROM   tbl_cus_contract
)
SELECT customerid,value_date,
       Count(first_occurrence_of_contract)
         OVER (PARTITION BY customerid 
              ORDER BY value_date ROWS unbounded preceding) - 1 AS count_contract
FROM   cte 

db<>fiddle:在這里試試

uj5u.com熱心網友回復：

這可以通過一個model子句來完成：

select *
from t
model
  /*The same as in analytic function*/
  partition by (customer_id)
  /*This would be used as ordering criteria later*/
  dimension by (dt)
  measures (
    /*Need to specify all the columns
      not in partition or dimension*/
    contract_id,
    0 as cnt
  )
  rules (
    cnt[any] =
      /*dt <= cv(dt) is all dates before
      the current value of date.
      The same as window specification*/
      count(distinct contract_id)[dt <= cv(dt)]
  )

客戶 ID | DT | 合同 ID | 碳納米管
:------------ | :-------- | ----------: | --:
一個 | 20 年 7 月 1 日 | 第1234章 1
一個 | 20-DEC-20 | 7896 | 2
C | 01-JUN-20 | 6578 | 1
C | 20-OCT-20 | 8990 | 2
C | 20 年 12 月 21 日 | 4789 | 3
C | 21 年 12 月 23 日 | 4789 | 3
乙| 09-SEP-21 | 3457 | 1

db<>在這里擺弄

UPD：如果contract_id每個日期有多個，您可以添加unique single reference，因此model不符合重復的維度值。查看更新的代碼和結果：

select *
from t
model
  /*The same as in analytic function*/
  partition by (customer_id)
  /*This would be used as ordering criteria later*/
  dimension by (dt)
  measures (
    /*Need to specify all the columns
      not in partition or dimension*/
    contract_id,
    0 as cnt
  )
  unique single reference
  rules (
    cnt[any] =
      /*dt <= cv(dt) is all dates before
      the current value of date.
      The same as window specification*/
      count(distinct contract_id)[dt <= cv(dt)]
  )

客戶 ID | DT | 合同 ID | 碳納米管
:------------ | :-------- | ----------: | --:
一個 | 20 年 7 月 1 日 | 第1234章 1
一個 | 20-DEC-20 | 7896 | 2
C | 01-JUN-20 | 6578 | 1
C | 20-OCT-20 | 8990 | 3
C | 20-OCT-20 | 9999 | 3
C | 20 年 12 月 21 日 | 4789 | 4
C | 21 年 12 月 23 日 | 4789 | 4
乙| 09-SEP-21 | 3457 | 1

db<>在這里擺弄

標籤：sql 甲骨文 数数 oracle-sqldeveloper 清楚的

上一篇：如何使用另一列的值更新特定列的每一行？

下一篇：根據條件洗掉行，SQLOracle