代码之家 › 专栏 › 技术社区 › Brock Adams

我如何才能用“所有其他”和合计按前n个类别进行合计?

sql-server-2017 tsql sql-server sql

Brock Adams · 技术社区 · 6 年前

我有按类别列出用户销售额的表(每个销售额至少有一个,可能有多个类别)。

我可以为一个用户获得最重要的类别,但我需要一个用户的统计数据 二者都 他/她的前n个类别和其余类别。

我把问题归结为 MCVE 如下…

麦克维 Data Summary 以下内容:

Salesman    SaleID    Amount    Categories
--------    ------    ------    ------------------------------
     1         1         2      Service
     2         2         2      Software, Support_Contract
     2         3         3      Service
     2         4         1      Parts, Service, Software
     2         5         3      Support_Contract
     2         6         4      Promo_Gift, Support_Contract
     2         7        -2      Rebate, Support_Contract
     3         8         2      Software, Support_Contract
     3         9         3      Service
     3        10         1      Parts, Software
     3        11         3      Support_Contract
     3        12         4      Promo_Gift, Support_Contract
     3        13        -2      Rebate, Support_Contract

MCVE安装程序SQL:

CREATE TABLE Sales      ([Salesman] int, [SaleID] int, [Amount] int);
CREATE TABLE SalesTags  ([SaleID] int, [TagId] int);
CREATE TABLE Tags       ([TagId] int, [TagName] varchar(100) );

INSERT INTO Sales
    ([Salesman], [SaleID], [Amount])
VALUES
    (1, 1, 2),        (2, 6, 4),        (3, 10, 1),
    (2, 2, 2),        (2, 7, -2),       (3, 11, 3),
    (2, 3, 3),        (3, 8, 2),        (3, 12, 4),
    (2, 4, 1),        (3, 9, 3),        (3, 13, -2),
    (2, 5, 3)
;
INSERT INTO SalesTags
    ([SaleID], [TagId])
VALUES
    (1, 3),           (6, 4),           (10, 1),
    (2, 1),           (6, 5),           (10, 2),
    (2, 4),           (7, 4),           (11, 4),
    (3, 3),           (7, 6),           (12, 4),
    (4, 1),           (8, 1),           (12, 5),
    (4, 2),           (8, 4),           (13, 4),
    (4, 3),           (9, 3),           (13, 6),
    (5, 4)
;
INSERT INTO Tags
    ([TagId], [TagName])
VALUES
    (1, 'Software'),
    (2, 'Parts'),
    (3, 'Service'),
    (4, 'Support_Contract'),
    (5, 'Promo_Gift'),
    (6, 'Rebate')
;

参见 this SQL Fiddle ,我可以获得用户的前n个标签,如:

WITH usersSales AS (  -- actual base CTE is much more complex
    SELECT  s.SaleID
            , s.Amount
    FROM    Sales s
    WHERE   s.Salesman = 2
)
SELECT Top 3  -- N can be 3 to 10
            t.TagName
            , COUNT (us.SaleID)     AS tagSales
            , SUM (us.Amount)       AS tagAmount
FROM        usersSales us
INNER JOIN  SalesTags st    ON st.SaleID = us.SaleID
INNER JOIN  Tags t          ON t.TagId   = st.TagId
GROUP BY    t.TagName
ORDER BY    tagAmount DESC
            , tagSales DESC
            , t.TagName

--显示用户的主要类别为:

“支持合同”
“服务”
“促销礼品”

按照这个顺序,对于用户2。(并支持用户3的合同、促销礼品、软件。)

但是 需要的结果 对于n=3,是:

用户2:

Top Category        Amount    Number of Sales
----------------    ------    ---------------
Support Contract       7             4
Service                4             2
Promo Gift             0             0
- All Others -         0             0
============================================
Totals                11             6

用户3:

Top Category        Amount    Number of Sales
----------------    ------    ---------------
Support Contract       7             4
Promo_Gift             0             0
Software               1             1
- All Others -         3             1
============================================
Totals                11             6

在哪里?

顶级类别 是给定销售的用户排名最高的类别(根据上面的查询)。
这个 顶级类别 第2行不包括第1行中已经核算的销售额。
这个 顶级类别 第3行不包括第1行和第2行中已占的销售额。
等。
所有未计入前n类的剩余销售额都归为 - All Others - 组。
底部的总数与用户的总销售额相匹配。

我如何汇总这样的结果?

请注意,这是在MS SQL Server 2017上运行的,我无法更改表模式。

2 回复 | 直到 6 年前

Vladimir Baranov 6 年前

WITH usersSales 
AS 
(  -- actual base CTE is much more complex
    SELECT
        s.SaleID
        , s.Amount
    FROM Sales s
    WHERE s.Salesman = 2
)
,CTE_Sums
AS
(
    SELECT
        t.TagName
        ,us.Amount
        ,us.SaleID
        ,SUM(us.Amount) OVER (PARTITION BY t.TagName) AS TagAmount
        ,COUNT(*) OVER (PARTITION BY t.TagName) AS TagSales
    FROM
        usersSales us
        INNER JOIN SalesTags st ON st.SaleID = us.SaleID
        INNER JOIN Tags t ON t.TagId = st.TagId
)
,CTE_Rank
AS
(
    SELECT
        TagName
        ,Amount
        ,SaleID
        ,TagAmount
        ,TagSales
        ,DENSE_RANK() OVER (ORDER BY TagAmount DESC, TagSales DESC, TagName) AS rnk
    FROM CTE_Sums
)
,CTE_Final
AS
(
    SELECT
        Main.TagName
        ,Main.Amount
        ,Main.SaleID
        ,Main.TagAmount
        ,Main.TagSales
        ,Main.rnk
        ,ISNULL(A.FinalTagAmount, 0) AS FinalTagAmount
        ,A.FinalTagSales
    FROM
        CTE_Rank AS Main
        OUTER APPLY
        (
            SELECT
                SUM(Detail.Amount) AS FinalTagAmount
                ,COUNT(*) AS FinalTagSales
            FROM CTE_Rank AS Detail
            WHERE
                Detail.rnk = Main.rnk
                AND Detail.SaleID NOT IN
                (
                    SELECT PrevRanks.SaleID
                    FROM CTE_Rank AS PrevRanks
                    WHERE PrevRanks.rnk < Detail.rnk
                )
        ) AS A
)
SELECT
    TagName
    ,MIN(FinalTagAmount) AS FinalTagAmount
    ,MIN(FinalTagSales) AS FinalTagSales
    ,rnk
    ,0 AS SortOrder
FROM CTE_Final
WHERE rnk <= 3
GROUP BY
    TagName
    ,rnk

UNION ALL

SELECT
    '- All Others -' AS TagName
    ,SUM(FinalTagAmount) AS FinalTagAmount
    ,SUM(FinalTagSales) AS FinalTagSales
    ,0 AS rnk
    ,1 AS SortOrder
FROM CTE_Final
WHERE rnk > 3

ORDER BY
    SortOrder
    ,rnk
;

SaleID

+------------------+--------+--------+-----------+----------+-----+
|     TagName      | Amount | SaleID | TagAmount | TagSales | rnk |
+------------------+--------+--------+-----------+----------+-----+
| Support Contract |     -2 |      7 |         7 |        4 |   1 |
| Support Contract |      3 |      5 |         7 |        4 |   1 |
| Support Contract |      4 |      6 |         7 |        4 |   1 |
| Support Contract |      2 |      2 |         7 |        4 |   1 |
| Service          |      1 |      4 |         4 |        2 |   2 |
| Service          |      3 |      3 |         4 |        2 |   2 |
| Promo Gift       |      4 |      6 |         4 |        1 |   3 |
| Software         |      1 |      4 |         3 |        2 |   4 |
| Software         |      2 |      2 |         3 |        2 |   4 |
| Parts            |      1 |      4 |         1 |        1 |   5 |
| Rebate           |     -2 |      7 |        -2 |        1 |   6 |
+------------------+--------+--------+-----------+----------+-----+

OUTER APPLY

+------------------+--------+--------+-----------+----------+-----+----------------+---------------+
|     TagName      | Amount | SaleID | TagAmount | TagSales | rnk | FinalTagAmount | FinalTagSales |
+------------------+--------+--------+-----------+----------+-----+----------------+---------------+
| Support Contract |     -2 |      7 |         7 |        4 |   1 |              7 |             4 |
| Support Contract |      3 |      5 |         7 |        4 |   1 |              7 |             4 |
| Support Contract |      4 |      6 |         7 |        4 |   1 |              7 |             4 |
| Support Contract |      2 |      2 |         7 |        4 |   1 |              7 |             4 |
| Service          |      1 |      4 |         4 |        2 |   2 |              4 |             2 |
| Service          |      3 |      3 |         4 |        2 |   2 |              4 |             2 |
| Promo Gift       |      4 |      6 |         4 |        1 |   3 |              0 |             0 |
| Software         |      1 |      4 |         3 |        2 |   4 |              0 |             0 |
| Software         |      2 |      2 |         3 |        2 |   4 |              0 |             0 |
| Parts            |      1 |      4 |         1 |        1 |   5 |              0 |             0 |
| Rebate           |     -2 |      7 |        -2 |        1 |   6 |              0 |             0 |
+------------------+--------+--------+-----------+----------+-----+----------------+---------------+

+------------------+----------------+---------------+-----+-----------+
|     TagName      | FinalTagAmount | FinalTagSales | rnk | SortOrder |
+------------------+----------------+---------------+-----+-----------+
| Support Contract |              7 |             4 |   1 |         0 |
| Service          |              4 |             2 |   2 |         0 |
| Promo Gift       |              0 |             0 |   3 |         0 |
| - All Others -   |              0 |             0 |   0 |         1 |
+------------------+----------------+---------------+-----+-----------+

Paul White 6 年前

创建表销售 ( saleid整数不为空, 金额整数不为空, 标记名varchar(100)不为空, 主键(标记名,saleid) ; --计算总数(用于最终输出) 创建表标记总数 ( 位置整数标识(1,1)不是空主键, 标记名varchar(100)空唯一, numsales bigint不为空, sumsales整数不为空, ;


   数据加载

——获取@personid的销售数据一次
插入销售
(
萨利德,
数量,
标记名
)
























-- Parameters
DECLARE
    @PersonId integer = 3,
    @TopN bigint = 3;

-- Holds sales data extract for @PersonId
CREATE TABLE #Sales
(
    SaleID integer NOT NULL,
    Amount integer NOT NULL,
    TagName varchar(100) NOT NULL,

    PRIMARY KEY (TagName, SaleID)
);

-- Computed totals (for final output)
CREATE TABLE #TagTotals
(
    Position integer IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    TagName varchar(100) NULL UNIQUE,
    NumSales bigint NOT NULL,
    SumSales integer NOT NULL,
);




-- Fetch sales data for the @PersonId once
INSERT #Sales
(
    SaleID,
    Amount,
    TagName
)
SELECT
    S.SaleID,
    S.Amount,
    T.TagName
FROM dbo.Sales AS S 
JOIN dbo.SalesTags AS ST
    ON ST.SaleID = S.SaleID
JOIN dbo.Tags AS T
    ON T.TagId = ST.TagId
WHERE 
    S.Salesman = @PersonId;




-- Find the @TopN top categories
INSERT #TagTotals
(
    TagName,
    NumSales,
    SumSales
)
SELECT
    S.TagName, 
    NumSales = COUNT_BIG(*), 
    SumSales = SUM(S.Amount)
FROM #Sales AS S
GROUP BY 
    S.TagName
ORDER BY 
    SumSales DESC, 
    NumSales DESC, 
    S.TagName ASC
OFFSET 0 ROWS 
FETCH FIRST @TopN ROWS ONLY;






-- Recalculate totals for categories with dependencies
UPDATE TT
SET NumSales = TagSales.NumSales, 
    SumSales = ISNULL(TagSales.SumSales, 0)
FROM #TagTotals AS TT
CROSS APPLY
(
    SELECT 
        NumSales = COUNT_BIG(*), 
        SumSales = SUM(S.Amount)
    FROM #Sales AS S
    WHERE
        -- For the current tag
        S.TagName = TT.TagName
        -- Exclude sales covered by previous tags
        AND S.SaleID NOT IN
        (
            SELECT
                S2.SaleID
            FROM #TagTotals AS PreviousTags
            JOIN #Sales AS S2
                ON S2.TagName = PreviousTags.TagName
            WHERE
                PreviousTags.Position < TT.Position
        )
) AS TagSales
-- First category has no exclusions to handle
WHERE
    TT.Position > 1;






-- Add '- All Others -' category
INSERT #TagTotals
(
    TagName,
    NumSales,
    SumSales
)
SELECT 
    '- All Others -', 
    NumSales = COUNT_BIG(*), 
    SumSales = ISNULL(SUM(S.Amount), 0)
FROM #Sales AS S
WHERE S.SaleID NOT IN
(
    -- Sales already accounted for
    SELECT
        S2.SaleID
    FROM #TagTotals AS O
    JOIN #Sales AS S2
        ON S2.TagName = O.TagName
);






-- Add grand total
INSERT #TagTotals
(
    TagName,
    NumSales,
    SumSales
)
SELECT 
    'Totals', 
    NumSales = ISNULL(SUM(O.NumSales), 0), 
    SumSales = ISNULL(SUM(O.SumSales), 0)
FROM #TagTotals AS O;






-- Final output
SELECT
    [Top Category] = O.TagName,
    [Amount] = O.SumSales,
    [Number of Sales] = O.NumSales 
FROM #TagTotals AS O 
ORDER BY
    O.Position ASC;




@PersonId = 2

ââââââââââââââââââââ¦âââââââââ¦ââââââââââââââââââ
â   Top Category   â Amount â Number of Sales â
â âââââââââââââââââââ¬âââââââââ¬ââââââââââââââââââ£
â Support_Contract â      7 â               4 â
â Service          â      4 â               2 â
â Promo_Gift       â      0 â               0 â
â - All Others -   â      0 â               0 â
â Totals           â     11 â               6 â
ââââââââââââââââââââ©âââââââââ©ââââââââââââââââââ


@PersonId = 3

ââââââââââââââââââââ¦âââââââââ¦ââââââââââââââââââ
â   Top Category   â Amount â Number of Sales â
â âââââââââââââââââââ¬âââââââââ¬ââââââââââââââââââ£
â Support_Contract â      7 â               4 â
â Promo_Gift       â      0 â               0 â
â Software         â      1 â               1 â
â - All Others -   â      3 â               1 â
â Totals           â     11 â               6 â
ââââââââââââââââââââ©âââââââââ©ââââââââââââââââââ


db<>fiddle