FInding的市场份额与年变化

Question

这里是数据库模式

​

​

案件问题：

2020年每个月的奶制品采购总量(即total_sales)?

• What是2020年每个月乳制品(占采购的所有产品)的总份额)(即2020年的market_share)?

• For )，与2019年同期相比，每月奶制品采购总额的增减百分比(即year_change)是多少？因此，它对这三大类(它们将其视为乳制品)感兴趣：“全脂牛奶”、“酸奶”和“家庭鸡蛋”。

说明:按月按升序排序查询。月和total_sales应表示为integers，而year_change应为rounded to two decimal places的百分比(如27.95%变为27.95%)。

您的查询将需要返回一个类似于以下内容的表，包括相同的列名。

​

​

以下是代码：

with purchases_2019 as (SELECT p1.month as month,COUNT(p1.purchase_id) as count_2
 FROM purchases_2019 as p1
 LEFT JOIN categories as cat ON p1.purchase_id=cat.purchase_id
 WHERE cat.category IN ('whole milk', 'yogurt' ,'domestic eggs')
 GROUP BY p1.month
 ORDER BY p1.month ASC),
    purchases_2020 as ( SELECT to_char(CAST(p2.fulldate AS DATE),'MM')::int as month, 
 COUNT(p2.purchaseid) as total_sales, 
 ROUND((COUNT(p2.purchaseid)*100::numeric/18277)::numeric,2) as market_share
 FROM purchases_2020 as p2
 LEFT JOIN categories as cat ON p2.purchaseid=cat.purchase_id 
 WHERE cat.category IN ('whole milk', 'yogurt' ,'domestic eggs')
 GROUP BY month
 ORDER BY month ASC)
    SELECT t2.month,t2.total_sales,t2.market_share,
    ROUND(((t2.total_sales-t1.count_2)*100::numeric/t1.count_2) ,2) as year_change
    FROM purchases_2020 as t2 
    INNER JOIN purchases_2019 as t1 ON t2.month=t1.month

结果如下：

​

​

但答案还是错的。我完全不知道。你能给我一些启示吗？谢谢

Answer 1

with p as 
(select 
    extract(month from to_date(b.full_date, 'YYYY/MM/DD')) as "month",
    sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end) as "old_sales"
from purchases_2019 b left join categories c 
    on b.purchase_id = c.purchase_id
group by 1
order by 1),

temp as
(select 
    extract(month from to_date(a.fulldate,'YYYY/MM/DD')) as "month",
    sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end) as "total_sales",
    round(100 * sum(case when c.category in ('whole milk', 'yogurt', 'domestic eggs') then 1 else 0 end)::numeric 
          / count(a.purchaseid),2) as "market_share"
from 
    purchases_2020 a left join categories c
        on a.purchaseid = c.purchase_id 
group by 1
order by 1)

select 
    temp.month, total_sales, market_share,
    round(100 * (total_sales - old_sales)::numeric / old_sales, 2) as "year_change"    
from temp left join p on temp.month = p.month;

Answer 2

为什么是18277？

本部分：

ROUND((COUNT(p2.purchaseid)*100::numeric/18277)::numeric,2) as market_share

在market_share计算中会有错误吗？

我认为在这个代码中，只计算了3类，但市场份额不应该是全部销售/3类销售吗？

只是个主意。