Oracle SQL按百分比范围查找填充

Question

我有一张表，上面有客户、购买日期和邮政编码。密钥是(customer_id、purchase_dt和zip_cd(

我正在努力寻找客户开展业务的邮政编码，范围包括80%及以上、60-80%、40-60%。有人能帮我查询一下吗。

with tmp as
(
select 123 as cust_id, date '2017-01-01' purchase_dt, '10035' zip_cd from dual
union
select 1234 as cust_id, date '2019-06-01' purchase_dt, '11377' zip_cd from dual
union
select 12345 as cust_id, date '2019-07-01' purchase_dt, '11377' zip_cd from dual
union
select 234 as cust_id, date '2019-08-01' purchase_dt, '11377' zip_cd from dual
union
select 2345 as cust_id, date '2019-09-01' purchase_dt, '11417' zip_cd from dual
)
select * from tmp;

预期输出：

80%及以上邮编：11377等等。

Answer 1

您可以使用average和analytical function count的组合，如下所示：

with tmp as
(
select 123 as cust_id, date '2017-01-01' purchase_dt, '10035' zip_cd from dual
union
select 1234 as cust_id, date '2019-06-01' purchase_dt, '11377' zip_cd from dual
union
select 12345 as cust_id, date '2019-07-01' purchase_dt, '11377' zip_cd from dual
union
select 234 as cust_id, date '2019-08-01' purchase_dt, '11377' zip_cd from dual
union
select 2345 as cust_id, date '2019-09-01' purchase_dt, '11417' zip_cd from dual
)
select zip_cd, 100*(count(1)/cnt) percntg from
(select zip_cd, count(1) over () cnt from tmp)
group by zip_cd, cnt
order by percntg desc;

Answer 2

如果客户在多天内进行了购买，并且不会重复计算，则此答案将考虑在内。此外，该回复添加了问题中讨论的分组：

with tmp as
(
select 123 as cust_id, date '2017-01-01' purchase_dt, '10035' zip_cd from dual
union
select 1234 as cust_id, date '2019-06-01' purchase_dt, '11377' zip_cd from dual
union
select 12345 as cust_id, date '2019-07-01' purchase_dt, '11377' zip_cd from dual
union
select 234 as cust_id, date '2019-08-01' purchase_dt, '11377' zip_cd from dual
union
select 2345 as cust_id, date '2019-09-01' purchase_dt, '11417' zip_cd from dual
)
SELECT sub2.pct_range, listagg(sub2.zip_cd||' ('||sub2.zip_pct||')', ', ') WITHIN GROUP (ORDER BY zip_pct DESC) AS ZIP_CODES
FROM (SELECT CASE
WHEN sub.zip_pct BETWEEN 80 AND 100 THEN '80% and above'
WHEN sub.zip_pct BETWEEN 60 AND 79 THEN '60% to 79%'
WHEN sub.zip_pct BETWEEN 40 AND 59 THEN '40% to 59%'
WHEN sub.zip_pct BETWEEN 20 AND 39 THEN '20% to 39%'
ELSE 'Below 20%' 
END AS PCT_RANGE,
sub.zip_cd,
sub.zip_pct
FROM (SELECT DISTINCT 
zip_cd, 
100*COUNT(DISTINCT cust_id) OVER (PARTITION BY zip_cd)/COUNT(DISTINCT cust_id) OVER () AS ZIP_PCT
FROM tmp) sub) sub2
GROUP BY pct_range
ORDER BY pct_range DESC;