現在我得到每個月的平均值
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
但是 SQL 查詢需要調整，所以total value current month - previous month
可能嗎？
每周
SELECT EXTRACT(WEEK FROM date_time) AS week, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(WEEK FROM date_time), EXTRACT(YEAR FROM date_time)
uj5u.com熱心網友回復：
是的，有可能：
SELECT t1.month, t2.year, t1.tot - t2.tot FROM
(
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t1
join (
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t2
on ((t1.year = t2.year) and (t1.month = t2.month 1)) or
((t1.year = t2.year 1) and (t1.month = 1) and (t2.month = 12))
我已將您的select并將其轉換為兩個子選擇，分別將它們命名為t1andt2并通過左連接的標準將它們連接起來。
請注意，當前第一個月不會有一對，如果您仍然需要它，那么您可以使用left joinandcoalesce來確保即使是未配對的專案也有一個“對”，而NULLfortot默認為 0。
進一步注意，您可以將此子查詢轉換為 aview以獲得更好的可讀性。
uj5u.com熱心網友回復：
如果我理解正確，您可以先按年和月對 avg(total) 進行分組，然后使用 LAG() 視窗函式獲取上個月的值，例如：
with my_table(date_time, total) as (
values
('2022-03-29', 10),
('2022-04-29', 12),
('2022-05-30', 20),
('2022-05-31', 30)
)
,grouped as (
SELECT EXTRACT('MONTH' FROM date_time::timestamp) AS month, EXTRACT('YEAR' FROM date_time::timestamp) AS year, avg("total") AS total
FROM my_table
GROUP BY EXTRACT('MONTH' FROM date_time::timestamp) , EXTRACT('YEAR' FROM date_time::timestamp)
)
SELECT *, LAG(total) OVER(ORDER BY year, month) as prev_month_total
FROM grouped
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484234.html
標籤：PostgreSQL
上一篇：在postgreSQL中使用正則運算式獲取特定型別的字串
下一篇：MERGE陳述句的鎖定級別