带有rank和pivot的sql需要每个分区中的rank和一个查询中的总rank

Question

我似乎无法正确理解这一点。我需要根据总组中rev_amount的最高总和对客户进行排名，然后根据bu_list中每个条目的收入总和进行排名——这可能吗？我之所以希望在一个查询中使用这个，是因为我在下面制作了一个示例值列表，但我的实际表有20000个客户和大约25个bu_id，所以我真的很简单。我一直在一个查询中摆弄排名和数据透视，但似乎无法正确处理。谢谢你的帮助。

CREATE TABLE bu_list ( bu_id int, bu_name varchar(50) );
CREATE TABLE cust_list ( cust_id int, rev_amount int, bu_id_fk int );
INSERT INTO bu_list VALUES (1, 'East');
INSERT INTO bu_list VALUES (2, 'West');
INSERT INTO bu_list VALUES (3, 'North');
INSERT INTO bu_list VALUES (4, 'South');
INSERT INTO cust_list VALUES (1, 1000, 1);
INSERT INTO cust_list VALUES (2, 2000, 1);
INSERT INTO cust_list VALUES  (3, 2000, 2);
INSERT INTO cust_list VALUES  (4, 2000, 3);
INSERT INTO cust_list VALUES  (5, 4000, 3);
INSERT INTO cust_list VALUES  (5, 100, 3);
INSERT INTO cust_list VALUES  (6, 5000, 4);
INSERT INTO cust_list VALUES  (7, 6000, 4);

预期输出为

cust_id |total_rank| east_rank| west_rank| north_rank| south_rank
7         1                                                1
6         2                                                2
5         3                                  1
4         4                                  2
3         4                         1
2         4              1
1         5              2 

Answer 1

您可以使用条件聚合：

SELECT
    cust_id,
    total_rank,
    east_rank   = MAX(CASE WHEN b.bu_id = 1 THEN t.bu_rank END),
    west_rank   = MAX(CASE WHEN b.bu_id = 2 THEN t.bu_rank END),
    north_rank  = MAX(CASE WHEN b.bu_id = 3 THEN t.bu_rank END),
    south_rank  = MAX(CASE WHEN b.bu_id = 4 THEN t.bu_rank END)
FROM (
    SELECT
        cust_id,
        bu_id_fk,
        total_rank  = DENSE_RANK() OVER(ORDER BY rev_amount DESC),
        bu_rank     = DENSE_RANK() OVER(PARTITION BY bu_id_fk ORDER BY rev_amount DESC)
    FROM cust_list
) t
INNER JOIN bu_list b
    ON b.bu_id = t.bu_id_fk
GROUP BY t.cust_id, t.total_rank
ORDER BY t.total_rank