從下面的表格中，我想計算每個客戶在上個季度有多少次預約。
dwh_schema.appointments
customer_id appointment id created_at
A 1 20210411 11。 15: 08
A 2 20210611 11: 15: 08
A 3 20210811 11: 15:08.
當前查詢
select customer_id。count(distinct appointment_id) as Last_quarter_appointments
from dwh_schema.appointments
where created_at::date >='2021-04-01' and created_at。 :date<='2021-06-30'
上述查詢的問題是，如果我明年再次運行它，資料將是不相關的。 因為最后一個季度不是動態的。
uj5u.com熱心網友回復：
你可以按年份和季度對資料進行分組。
通過使用extract方法，你可以得到日期的年份和季度。
select customer_id, extract(year from created_at: :date) as year, extract(quarter from created_at::date) as quarter, count(distinct appointment_id) as last_quarter_appointmentsfrom dwh_schema.appointments
group by extract(year from created_at: :date), extract(quarter from created_at::date), customer_id
having extract(year from created_at::date) = extract(year from now() and extract(quarter from created_at::date) = extract(quarter from now() - 1。
然后通過having子句做過濾。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/324737.html
標籤：