现在我得到每个月的平均值
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)
是的,有可能:
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
并将其转换为两个子选择,分别将它们命名为t1
andt2
并通过左连接的标准将它们连接起来。
请注意,第一个月目前还没有配对,如果您仍然需要它,那么您可以使用left join
andcoalesce
来确保即使是未配对的项目也有一个“配对”,并且NULL
fortot
默认为 0。
进一步注意,您可以将此子查询转换为 aview
以获得更好的可读性。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句