postgresql - 年销售额变化

Question

到目前为止，我使用连接查询：数量 - 购买的商品数量，日期 - 交易日期，名称 - 客户名称，年份 - 交易年份。和日变类似，我尝试使用年差，传入查询满足条件。

SELECT s1.year, s1.name, sum(s1.quantity)
FROM T1 s1
JOIN T2 s2
ON s1.date::timestamp - s2.date::timestamp = '1 year'
WHERE (s1.quantity > s2.quantity AND s1.year BETWEEN 2015 and 2017)
OR (s1.quantity < s2.quantity AND s1.year BETWEEN 2018 and 2019)
GROUP BY 1,2

标签： postgresql

Answer 1

with data as (
    SELECT name,
        COALESCE(SUM(CASE WHEN year = 2015 THEN qty ELSE 0 END), 0) AS total2015,
        COALESCE(SUM(CASE WHEN year = 2016 THEN qty ELSE 0 END), 0) AS total2016,
        COALESCE(SUM(CASE WHEN year = 2017 THEN qty ELSE 0 END), 0) AS total2017,
        COALESCE(SUM(CASE WHEN year = 2018 THEN qty ELSE 0 END), 0) AS total2018,
        COALESCE(SUM(CASE WHEN year = 2019 THEN qty ELSE 0 END), 0) AS total2019
    FROM sajith_gowthaman.practice_annual_inc1
    WHERE year BETWEEN 2015 and 2019
    GROUP BY name
)
select *
from data
where   total2015 < total2016
    and total2016 < total2017
    and total2018 > total2019;