postgresql 我不能正确地将CTE的一个结果包含到查询中，也不知道如何做到这一点

rryofs0p  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(126)

我创建了一个查询，以检索特定部门在特定年份的销售额、特定国家和年份的所有部门的销售额，以及特定国家部门销售额与销售额的百分比。我需要计算上一年和本年度的百分比之间的差异（1998年为1999年，1999年为2000年等）。）。

Americas    1999    Direct Sales    7932550.86  12464862.43 63.64%
Americas    1999    Internet    1377051.66  12464862.43 11.05%
Americas    1999    Partners    3155259.91  12464862.43 25.31%
    Asia    1999    Direct Sales    1369875.55  2395268.48  57.19%
    Asia    1999    Internet    285153.52   2395268.48  11.90%
    Asia    1999    Partners    740239.41   2395268.48  30.90%
    Europe  1999    Direct Sales    4755014.09  6502887.40  73.12%
    Europe  1999    Internet    511273.40   6502887.40  7.86%
    Europe  1999    Partners    1236599.91  6502887.40  19.02%
Americas    2000    Direct Sales    7967141.89  13788324.32 57.78%
Americas    2000    Internet    1162343.86  13788324.32 8.43%
Americas    2000    Partners    4658838.57  13788324.32 33.79%
    Asia    2000    Direct Sales    1242257.78  2343114.67  53.02%
    Asia    2000    Internet    242492.77   2343114.67  10.35%
    Asia    2000    Partners    858364.12   2343114.67  36.63%
    Europe  2000    Direct Sales    4493735.78  6737500.98  66.70%
    Europe  2000    Internet    415135.09   6737500.98  6.16%
    Europe  2000    Partners    1828630.11  6737500.98  27.14%

该表包括1999年至2000年的资料。
前几年的第二个：

Americas    1998    Direct Sales    8340188.74  13468418.60 61.92%
Americas    1998    Internet    1714392.70  13468418.60 12.73%
Americas    1998    Partners    3155712.64  13468418.60 23.43%
Americas    1998    Tele Sales  258124.52   13468418.60 1.92%
    Asia    1998    Direct Sales    1409654.80  2468506.67  57.11%
    Asia    1998    Internet    320522.72   2468506.67  12.98%
    Asia    1998    Partners    734810.33   2468506.67  29.77%
    Asia    1998    Tele Sales  3518.82         2468506.67  .14%
    Europe  1998    Direct Sales    5101419.96  7102517.23  71.83%
    Europe  1998    Internet    686048.60   7102517.23  9.66%
    Europe  1998    Partners    1301521.63  7102517.23  18.32%
    Europe  1998    Tele Sales  13527.04    7102517.23  .19%
Americas    1999    Direct Sales    7932550.86  12464862.43 63.64%
Americas    1999    Internet    1377051.66  12464862.43 11.05%
Americas    1999    Partners    3155259.91  12464862.43 25.31%
    Asia    1999    Direct Sales    1369875.55  2395268.48  57.19%
    Asia    1999    Internet    285153.52   2395268.48  11.90%
    Asia    1999    Partners    740239.41   2395268.48  30.90%
    Europe  1999    Direct Sales    4755014.09  6502887.40  73.12%
    Europe  1999    Internet    511273.40   6502887.40  7.86%
    Europe  1999    Partners    1236599.91  6502887.40  19.02%

我想要的输出是：

Americas    1999    Direct Sales    7932550.86  12464862.43 63.64%    61.92%
Americas    1999    Internet    1377051.66  12464862.43 11.05%    12.73%
Americas    1999    Partners    3155259.91  12464862.43 25.31%    23.43%
    Asia    1999    Direct Sales    1369875.55  2395268.48  57.19%    57.11%
    Asia    1999    Internet    285153.52   2395268.48  11.90%    12.98%
    Asia    1999    Partners    740239.41   2395268.48  30.90%    29.77%
    Europe  1999    Direct Sales    4755014.09  6502887.40  73.12%    71.83%
    Europe  1999    Internet    511273.40   6502887.40  7.86%     9.66%
    Europe  1999    Partners    1236599.91  6502887.40  19.02%    18.32%
Americas    2000    Direct Sales    7967141.89  13788324.32 57.78%    63.64%
Americas    2000    Internet    1162343.86  13788324.32 8.43%     11.05%
Americas    2000    Partners    4658838.57  13788324.32 33.79%    25.31%
    Asia    2000    Direct Sales    1242257.78  2343114.67  53.02%    57.19%
    Asia    2000    Internet    242492.77   2343114.67  10.35%    11.90%
    Asia    2000    Partners    858364.12   2343114.67  36.63%    30.90%
    Europe  2000    Direct Sales    4493735.78  6737500.98  66.70%    73.12%
    Europe  2000    Internet    415135.09   6737500.98  6.16%     7.86%
    Europe  2000    Partners    1828630.11  6737500.98  27.14%    19.02%

最后的差异，意思是（val 1-val 2）。
但我收到的是：

Americas    1999    Direct Sales    23797652.58 37394587.29 63.64%  57.78%
Americas    1999    Direct Sales    23797652.58 37394587.29 63.64%  61.92%
Americas    1999    Direct Sales    23797652.58 37394587.29 63.64%  63.64%
Americas    1999    Internet    4131154.98  37394587.29 11.05%  11.05%
Americas    1999    Internet    4131154.98  37394587.29 11.05%  12.73%
Americas    1999    Internet    4131154.98  37394587.29 11.05%  8.43%
Americas    1999    Partners    9465779.73  37394587.29 25.31%  23.43%
Americas    1999    Partners    9465779.73  37394587.29 25.31%  25.31%
Americas    1999    Partners    9465779.73  37394587.29 25.31%  33.79%
    Asia    1999    Direct Sales    4109626.65  7185805.44  57.19%  53.02%
    Asia    1999    Direct Sales    4109626.65  7185805.44  57.19%  57.11%
    Asia    1999    Direct Sales    4109626.65  7185805.44  57.19%  57.19%
    Asia    1999    Internet    855460.56   7185805.44  11.90%  10.35%
    Asia    1999    Internet    855460.56   7185805.44  11.90%  11.90%
    Asia    1999    Internet    855460.56   7185805.44  11.90%  12.98%
    Asia    1999    Partners    2220718.23  7185805.44  30.90%  29.77%
    Asia    1999    Partners    2220718.23  7185805.44  30.90%  30.90%
    Asia    1999    Partners    2220718.23  7185805.44  30.90%  36.63%
    Europe  1999    Direct Sales    14265042.27 19508662.20 73.12%  66.70%
    Europe  1999    Direct Sales    14265042.27 19508662.20 73.12%  71.83%
    Europe  1999    Direct Sales    14265042.27 19508662.20 73.12%  73.12%
    Europe  1999    Internet    1533820.20  19508662.20 7.86%   6.16%
    Europe  1999    Internet    1533820.20  19508662.20 7.86%   7.86%
    Europe  1999    Internet    1533820.20  19508662.20 7.86%   9.66%
    Europe  1999    Partners    3709799.73  19508662.20 19.02%  18.32%
    Europe  1999    Partners    3709799.73  19508662.20 19.02%  19.02%
    Europe  1999    Partners    3709799.73  19508662.20 19.02%  27.14%

对于美洲，在最后一列的前3行中，它给出了2000年的结果，然后是1998年，然后是1999年，而在前一列中，它只根据需要给出了1999年的产出。
我使用的代码：

WITH previous_percentage AS(SELECT DISTINCT c3.region AS cr, t.year AS cy, c2.channel AS cd,
        TO_CHAR(sum(s.amount) OVER(PARTITION BY c2.channel, t.year, c3.region )
        /
        sum(s.amount_sold) OVER(PARTITION BY t.year, c3.region ) * 100, 'fm99D00%') AS previous_year
        FROM sh.sales s 
        JOIN sh.customers c ON s.cust_id = c.cust_id 
        JOIN sh.times t ON s.time_id = t.time_id 
        JOIN sh.channels c2 ON s.channel_id = c2.channel_id 
        JOIN sh.countries c3 ON c.country_id = c3.country_id 
        WHERE c3.region IN ('Americas', 'Europe','Asia') AND t.year IN ('1998','1999','2000')
        )   
SELECT DISTINCT c3.region, t.year, c2.channel,
sum(s.amount) OVER(PARTITION BY c2.channel, t.year, c3.region ) AS amount,
sum(s.amount) OVER(PARTITION BY t.year, c3.region ) AS whole_sum,
TO_CHAR(sum(s.amount) OVER(PARTITION BY c2.channel, t.year, c3.country_region )
/
sum(s.amount) OVER(PARTITION BY t.year, c3.region ) * 100, 'fm99D00%') AS percent_channels,
p.previous_year
FROM sh.sales s 
JOIN sh.customers c ON s.cust_id = c.cust_id 
JOIN sh.times t ON s.time_id = t.time_id 
JOIN sh.channels c2 ON s.channel_id = c2.channel_id 
JOIN sh.countries c3 ON c.country_id = c3.country_id 
JOIN previous_percentage p ON p.cr = c3.region AND p.cd = c2.channel 
WHERE c3.region IN ('Americas', 'Europe','Asia') AND t.year IN ('1999','2000','2001')
ORDER BY t.year ASC 
;

我试图在CTE内操作查询，但无法得到任何有用的东西，我还认为有问题的日期是不同的，我怀疑这是主要问题，但我不知道如何修复它。我想我在某个地方犯了个错误，但我找不到。

postgresql

1条答案

按热度按时间
am46iovg

am46iovg1#

窗口函数是你的朋友。试试这个：

with t as
(
 select *, 
 lag(percentage) over (partition by area, department order by "year") as percentage_lag
 from the_table
)
select *, percentage - percentage_lag as percentage_dif
from t where percentage_lag is not null;

详情请参阅DB-fiddle。

赞(0）分享  回复(0）举报  2023-04-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com