計算具有最小日期Postgres的唯一資料-有解無憂

我在嘗試計算表中的資料時遇到問題。

例如我的桌子看起來像這樣：

table = test
id       | transaction_date
1        | '2021-10-01 00:00:00'
2        | '2021-10-01 00:00:00'
3        | '2021-10-02 00:00:00'
4        | '2021-10-03 00:00:00'
5        | '2021-10-04 00:00:00'
6        | '2021-10-05 00:00:00'
7        | '2021-10-06 00:00:00'

1        | '2021-11-01 00:00:00'
2        | '2021-11-01 00:00:00'
3        | '2021-11-02 00:00:00'

1        | '2021-12-01 00:00:00'
2        | '2021-12-01 00:00:00'
3        | '2021-12-02 00:00:00'
4        | '2021-12-03 00:00:00'
8        | '2021-12-04 00:00:00'
8        | '2021-12-05 00:00:00'
9        | '2021-12-06 00:00:00'
9        | '2021-12-07 00:00:00'

我嘗試使用此查詢

WITH first_transaction_AS (SELECT MIN(transaction_date), id FROM test),
previous_month AS (SELECT DISTINCT idFROM test 
WHERE transaction_date >= date_trunc('month', transaction_date)-interval '1 month' 
AND transaction_date < date_trunc('month', transaction_date)

-- WHERE clause for previous month will be dynamic

SELECT date_trunc('month', transaction_date), COUNT(DISTINCT id)
FROM test 
WHERE test.id NOT IN (SELECT id FROM previous_month) AND transaction_date NOT IN (SELECT min FROM first_transaction)

我想實作一個聚合查詢，其中： Count ID Where ID has Transaction this month AND ID without Transaction Last Month AND It's not the ID's First Transaction

目前的問題是，ID 8 和 9 仍然會被計數，因為他們在同月的第一筆交易之后還有一筆交易。

所以我的目標是：

    Date                    |         Count
  2021-12-01 00:00:00       |           1  (4)

uj5u.com熱心網友回復：

根據規則只有1個。

select 
  date(date_trunc('month', transaction_date)) as "Date"
, count(distinct id) as "Count"
from test t
where transaction_date >= date_trunc('month', current_date)
  and transaction_date < (date_trunc('month', current_date) interval '1 month') 
  and not exists ( -- not previous month
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date >= date_trunc('month', current_date) - interval '1 month'
        and t2.transaction_date <  date_trunc('month', current_date)
  
  )
  and exists ( -- created previously
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date <  date_trunc('month', current_date)
  )
group by date_trunc('month', transaction_date)

日期 | 數數
:--------- | ----:
2021-12-01 | 1

關于db<>fiddle 的演示在這里

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

標籤：sql PostgreSQL的

上一篇：無法在Mac上重新安裝postgresql

下一篇：即使時間范圍不包含任何記錄，多列orderby也會殺死查詢性能