在SQL中对数据进行分区时Agg函数

Question

我有一个如下所示的表：

store_id   industry_id   cust_id   amount   gender
1          100           1000      1.00     M
2          100           1000      2.05     M
3          100           1000      3.15     M
4          100           1000      4.00     M
5          100           2000      5.00     F
6          200           2000      5.20     F
7          200           5000      6.05     F
8          200           6000      7.10     F

以下是创建此表的代码：

CREATE TABLE t1(
store_id int,
industry_id int,
cust_id int,
amount float,
gender char
);
INSERT INTO t1 VALUES(1,100,1000,1.00, 'M');
INSERT INTO t1 VALUES(2,100,1000,2.05, 'M');
INSERT INTO t1 VALUES(3,100,1000,3.15, 'M');
INSERT INTO t1 VALUES(4,100,1000,4.00, 'M');
INSERT INTO t1 VALUES(5,100,2000,5.00, 'F');
INSERT INTO t1 VALUES(6,200,2000,5.20, 'F');
INSERT INTO t1 VALUES(7,200,5000,6.05, 'F');
INSERT INTO t1 VALUES(8,200,6000,7.10, 'F');

我想回答的问题是：按行业划分，前20%客户的平均交易金额是多少

这应该会产生以下结果：

store_id.     industry_id      avg_amt_top_20
1             100              4.80
2             100              4.80
3             100              4.80
4             100              4.80
5             100              4.80
6             200              7.10
7             200              7.10
8             200              7.10

到目前为止，我拥有的是：

SELECT
store_id, industry_id,
avg(CASE WHEN percentile>=0.80 THEN amount ELSE NULL END) OVER(PARTITION BY industry_id)  as cust_avg
FROM(
SELECT store_id, industry_id, amount, cume_dist() OVER(
PARTITION BY industry_id 
ORDER BY amount desc) AS percentile 
FROM t1
) tmp
GROUP BY store_id, industry_id;

这在GROUP BY(包含未聚合的列"amount"(上失败。最好的方法是什么？

Answer 1

按行业划分，前20%客户的平均交易金额是多少？

基于这个问题，我不明白为什么store_id会出现在结果中。

如果我理解正确的话，您需要进行汇总以获得客户的总数。然后您可以使用NTILE()来确定前20%。最后一步是按行业进行汇总：

SELECT industry_id, AVG(total)
FROM (SELECT customer_id, industry_id, SUM(amount) as total,
NTILE(5) OVER (PARTITION BY industry_id ORDER BY SUM(amount) DESC) as tile
FROM t
GROUP BY customer_id, industry_id
) t
WHERE tile = 1
GROUP BY industry_id