代码之家 › 专栏 › 技术社区 › Alex Czarto

按行数模的SQL分组

group-by tsql sql

Alex Czarto · 技术社区 · 16 年前

我有以下示例数据:

Id     Name     Quantity
1      Red      1
2      Red      3
3      Blue     1
4      Red      1
5      Yellow   3

所以在这个例子中,总共有5个红色、1个蓝色和3个黄色。我正在寻找一种按颜色分组的方法,但每组最多有2个项目(排序并不重要)。就像这样:

Name     QuantityInPackage
Red      2
Red      2
Red      1
Blue     1
Yellow   2
Yellow   1

对于如何在MS-SQL 2005上使用T-SQL来完成这一点有什么建议吗?

4 回复 | 直到 16 年前

Peter Lang 16 年前

我会定义一个包含序列号的表,比如 1 到 1000 并联接该表(除非您的数据库支持在查询中生成这些数字,如Oracle使用 CONNECT BY ):

表格编号

n
1
2
3
...

我使用Oracle尝试了以下查询(也应该使用TSQL):

With summed_colors As (
  Select name, Sum(quantity) quantity
  From colors
  Group By name
)
Select
  name,
  Case When n*2-1 = quantity Then 1 Else 2 End quantityInPackage
From summed_colors
Join nums On ( n*2-1 <= quantity )
Order By name, quantityInPackage Desc

它又回来了

Blue   1
Red    2
Red    2
Red    1
Yellow 2
Yellow 1

Cade Roux 16 年前

您需要使用数字表取消数据透视以生成多行:

DECLARE @PackageSize AS int
SET @PackageSize = 2

DECLARE @numbers AS TABLE (Number int)
INSERT  INTO @numbers
VALUES  (1)
INSERT  INTO @numbers
VALUES  (2)
INSERT  INTO @numbers
VALUES  (3)
INSERT  INTO @numbers
VALUES  (4)
INSERT  INTO @numbers
VALUES  (5)
INSERT  INTO @numbers
VALUES  (6)
INSERT  INTO @numbers
VALUES  (7)
INSERT  INTO @numbers
VALUES  (8)
INSERT  INTO @numbers
VALUES  (9)
INSERT  INTO @numbers
VALUES  (10)

DECLARE @t AS TABLE
    (
     Id int
    ,Nm varchar(6)
    ,Qty int
    )
INSERT  INTO @t
VALUES  (1, 'Red', 1)
INSERT  INTO @t
VALUES  (2, 'Red', 3)
INSERT  INTO @t
VALUES  (3, 'Blue', 1)
INSERT  INTO @t
VALUES  (4, 'Red', 1)
INSERT  INTO @t
VALUES  (5, 'Yellow', 3) ;
WITH    Totals
          AS (
              SELECT    Nm
                       ,SUM(Qty) AS TotalQty
                       ,SUM(Qty) / @PackageSize AS NumCompletePackages
                       ,SUM(Qty) % @PackageSize AS PartialPackage
              FROM      @t
              GROUP BY  Nm
             )
    SELECT  Totals.Nm
           ,@PackageSize AS QuantityInPackage
    FROM    Totals
    INNER JOIN @numbers AS numbers
            ON numbers.Number <= Totals.NumCompletePackages
    UNION ALL            
    SELECT  Totals.Nm
           ,PartialPackage AS QuantityInPackage
    FROM    Totals
    WHERE PartialPackage <> 0

Aaronaught 16 年前

这里的难点不是分组或模/除,而是需要做一个聚合(和),然后再分解数据。实际上没有任何“红色2”行,您必须以某种方式创建它们。

对于SQL Server 2005+,我可能会使用一个函数来执行“爆炸”操作:

CREATE FUNCTION dbo.CreateBuckets
(
    @Num int,
    @MaxPerGroup int
)
RETURNS TABLE
AS RETURN
WITH First_CTE AS
(
    SELECT CASE
        WHEN @MaxPerGroup < @Num THEN @MaxPerGroup
        ELSE @Num
    END AS Seed
),
Sequence_CTE AS
(
    SELECT Seed AS [Current], Seed AS Total
    FROM First_CTE

    UNION ALL

    SELECT
        CASE
            WHEN (Total + @MaxPerGroup) > @Num THEN (@Num - Total)
            ELSE @MaxPerGroup
        END,
        Total + @MaxPerGroup
    FROM Sequence_CTE
    WHERE Total < @Num
)
SELECT [Current] AS Num
FROM Sequence_CTE

然后,在主查询中,首先对数据进行分组(求和),然后使用bucket函数:

WITH Totals AS
(
    SELECT Name, SUM(Quantity) AS Total
    FROM Table
    GROUP BY Name
)
SELECT Name, b.Num AS QuantityInPackage
FROM Totals
CROSS APPLY dbo.CreateBuckets(Total, 2) b

这应该适用于任何桶大小,不必是2(只需更改参数)。

K Richard 16 年前

这是非常粗糙的,但它起作用。

CREATE TABLE #Colors
    (
    Id int,
    Name varchar(50),
    Quantity int
    )

INSERT INTO #Colors VALUES (1, 'Red', 1)
INSERT INTO #Colors VALUES (2, 'Red', 3)
INSERT INTO #Colors VALUES (3, 'Blue', 1)
INSERT INTO #Colors VALUES (4, 'Red', 1)
INSERT INTO #Colors VALUES (5, 'Yellow', 3)
INSERT INTO #Colors VALUES (6, 'Green', 2)

SELECT
    Name,
    SUM(Quantity) AS TotalQuantity
INTO #Summed
FROM
    #Colors
GROUP BY
    Name

SELECT
    Name,
    TotalQuantity / 2 AS RecordsWithQuantity2,
    TotalQuantity % 2 AS RecordsWithQuantity1
INTO #SortOfPivot
FROM
    #Summed
ORDER BY
    Name

DECLARE @RowCount int
SET @RowCount = (SELECT COUNT(*) FROM #SortOfPivot)
DECLARE @Name varchar(50)
DECLARE @TwosInsertCount int
DECLARE @OnesInsertCount int

CREATE TABLE #Result (Name varchar(50), Quantity int)

WHILE @RowCount > 0
BEGIN
    SET @Name = (SELECT TOP 1 Name FROM #SortOfPivot)
    SET @TwosInsertCount = (SELECT TOP 1 RecordsWithQuantity2 FROM #SortOfPivot)
    SET @OnesInsertCount = (SELECT TOP 1 RecordsWithQuantity1 FROM #SortOfPivot)
    WHILE @TwosInsertCount > 0
    BEGIN
        INSERT INTO #Result (Name, Quantity) VALUES (@Name, 2)
        SET @TwosInsertCount = @TwosInsertCount - 1
    END
    WHILE @OnesInsertCount > 0
    BEGIN
        INSERT INTO #Result (Name, Quantity) VALUES (@Name, 1)
        SET @OnesInsertCount =  @OnesInsertCount - 1
    END
    DELETE FROM #SortOfPivot WHERE Name = @Name
    SET @RowCount = (SELECT COUNT(*) FROM #SortOfPivot)
END

SELECT * FROM #Result
DROP TABLE #Colors
DROP TABLE #Result
DROP TABLE #Summed
DROP TABLE #SortOfPivot