T-SQL calcula el porcentaje del grupo de recuento por fecha

Resuelto isakavis asked hace 7 meses • 1 respuestas

Tengo una tabla que tiene la siguiente estructura y estoy intentando escribir un T-SQL para calcular el porcentaje de los recuentos. El porcentaje de cada fila debe ser el porcentaje del total de uno de los grupos que en mi caso es la fecha.

CREATE TABLE ProductSales
(
    Id int IDENTITY(1,1) NOT NULL,
    AccountId int NOT NULL,
    SaleDate date NOT NULL,
    ProductType varchar(50) NOT NULL,
    ProductSubType varchar(50) NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_ProductSales 
        PRIMARY KEY CLUSTERED (Id ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO

INSERT INTO ProductSales (AccountId, SaleDate, ProductType, ProductSubType, Amount) 
VALUES  (1, CAST(N'2023-11-01' AS Date), N'Books', N'Comics', 12.0000),
        (2, CAST(N'2023-11-01' AS Date), N'Books', N'Comics', 22.0000),
        (3, CAST(N'2023-11-01' AS Date), N'Clothing', N'Polos', 2.0000),
        (4, CAST(N'2023-11-01' AS Date), N'Books', N'Fonics', 18.0000),
        (5, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jackets', 22.0000),
        (5, CAST(N'2023-11-02' AS Date), N'Clothing', N'Polos', 18.0000),
        (2, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jeans', 19.0000),
        (1, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jackets', 187.0000)

Me gustaría la última columna como porcentaje del total de filas para cada día como se muestra a continuación. El cálculo del porcentaje se realizó manualmente y sin utilizar T-SQL.

SaleDate    ProductType ProductSubType  Total   Percentage
----------------------------------------------------------
11/1/2023   Books   Comics  2   50.00%
11/1/2023   Books   Fonics  1   25.00%
11/1/2023   Clothing    Polos   1   25.00%

11/2/2023   Books   Comics  2   33.33%
11/2/2023   Clothing    Jackets 2   33.33%
11/2/2023   Clothing    Jeans   1   16.67%
11/2/2023   Clothing    Polos   1   16.67%

Aquí está mi consulta que probé. Pero el porcentaje es el total de todas las filas. Lo necesito por fecha.

DECLARE @startdt date = '2023-11-01', 
        @enddt date = '2023-12-13'

SELECT
    CAST(SaleDate as date) SaleDate, ProductType, ProductSubType, 
    COUNT(1) Total,
    (100.0 * COUNT(1) / (SELECT COUNT(1) FROM ProductSales 
                         WHERE SaleDate BETWEEN @startdt AND @enddt)) AS 'Percentage'
FROM
    ProductSales 
WHERE
    SaleDate BETWEEN @startdt AND @enddt
GROUP BY 
    CAST(SaleDate AS date), ProductType, ProductSubType
ORDER BY
    SaleDate, ProductType
isakavis avatar Feb 16 '24 03:02 isakavis
Aceptado

Debe correlacionar la tabla de subconsultas con la principal, haciendo WHERE internalTable.date = externalTable.date.

SELECT
    SaleDate, ProductType, ProductSubType, 
    COUNT(*) Total,
    (100.0 * COUNT(*) / (SELECT COUNT(*) FROM ProductSales ps2
                         WHERE ps2.SaleDate = ps.SaleDate
                       --  group by ps.SaleDate
                         )) AS 'Percentage'
FROM
    ProductSales ps
WHERE
    SaleDate BETWEEN @startdt AND @enddt
GROUP BY 
    SaleDate, ProductType, ProductSubType
ORDER BY
    SaleDate, ProductType

Tenga cuidado con el grupo por columnas allí.

Pero también puedes agrupar lo anterior y simplemente usar la agregación en ventana para evitar la subconsulta por completo:

SELECT
    SaleDate, ProductType, ProductSubType, 
    COUNT(*) Total,
    100.0 * COUNT(*) / SUM(COUNT(*)) OVER(PARTITION BY SaleDate) AS 'Percentage'
FROM
    ProductSales ps
WHERE
    SaleDate BETWEEN @startdt AND @enddt
GROUP BY 
    SaleDate, ProductType, ProductSubType
ORDER BY
    SaleDate, ProductType

SUM(COUNT(*)) OVER(PARTITION BY SaleDate)resume los recuentos y genera la suma total que puede utilizar para obtener el porcentaje

siggemannen avatar Feb 15 '2024 21:02 siggemannen