计算占我销售额50%的产品数量

Question

我有一个在不同国家销售产品的商店。

我最终得到这样一个销售表(有更多的月)

<表类> 月 国家 产品销售tbody><<tr>01 - 2022英国番茄1001 - 2022英国香蕉401 - 2022英国大蒜101 - 2022FR番茄101 - 2022FR香蕉201 - 2022FR大蒜1

我终于找到了调整窗口函数的解决方案。

,t1 AS (
SELECT 
*
,SUM(sales) OVER (PARTITION BY country_group, order_date ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) AS running_total 
,0.5*SUM(sales) OVER(PARTITION BY country_group, order_date) AS total_sales_x_50perc
FROM t0
ORDER BY 1
)

SELECT 
order_date
,country_group
,COUNT(DISTINCT CASE WHEN running_total <= total_sales_x_50perc THEN product ELSE NULL END) AS nb_products
,COUNT(DISTINCT product) AS total_nb_products
,COUNT(DISTINCT CASE WHEN running_total <= total_sales_x_50perc THEN product ELSE NULL END)/COUNT(DISTINCT products) AS perc
FROM t1
GROUP BY 1,2
ORDER BY 1