SQL Get MODE Instead of AVG

Question

我有一个存储过程，它返回产品的平均发行价格。每个问题将有多行，因此我按条形码分组，以及为该问题插入一行的商店数量，并获得IssuePrice的平均值，因为这来自用户输入:

ALTER PROCEDURE [dbo].[GetUnknownBarcodeReport]  
@Status INT,@StoreTypeID INT
AS
BEGIN
SELECT COUNT(StoreCode) AS [# Stores]
    ,MAX(IssueName) AS IssueName
    ,AVG(IssuePrice) AS IssuePrice
    ,Barcode
    ,Product.EAN13 AS [Matched Product Code]
    ,Product.Name AS [Matched Product Name]
    ,Product.MainCatagory AS [Product Catagory]
FROM UnknownBarcodes
LEFT JOIN Product on LEFT(UnknownBarcodes.Barcode,13) = Product.EAN13
WHERE UnknownBarcodeStatusID = @Status
AND LEN(Barcode) >= 10
AND StoreTypeID = @StoreTypeID
GROUP BY Barcode, Product.EAN13, Product.Name, Product.MainCatagory
ORDER BY
    CASE WHEN @status = 1 THEN
        COUNT(StoreCode)
        WHEN @status = 2 THEN
        COUNT(StoreCode)
        WHEN @status = 3 THEN
        MAX(DateInserted)
    END DESC
 END

同一产品可以多次报告，因此有多行，但有时用户会输入不同的价格。如果十个用户都输入相同的条形码，并说价格是100，那么AVG(IssuePrice)也是100。但是，如果9输入100，最后一行输入1，则AVG更改为90.1

我想用MODE的数学等价替换AVG，这样上面的例子仍然会返回100，因为报告的100比任何其他值都多。这在SQL中可能吗?

为了帮助重新创建这个表，表的脚本如下:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UnknownBarcodes](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StoreCode] [int] NULL,
    [DateInserted] [datetime] NULL,
    [StoreTypeID] [int] NULL,
    [Barcode] [varchar](100) NULL,
    [UnknownBarcodeStatusID] [int] NULL,
    [StatusDescription] [varchar](1000) NULL,
    [IssueName] [varchar](100) NULL,
    [IssuePrice] [int] NULL,
    [AutoReported] [bit] NULL,
 CONSTRAINT [PK_UnknownBarcodes] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
    [EAN13] [bigint] NOT NULL,
    [Name] [varchar](250) NULL,
    [MainCatagory] [varchar](100) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [EAN13] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (412,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',200,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (843,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (860,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (864,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (964,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (1061,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (1350,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (1375,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (1489,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)
INSERT INTO [dbo].[UnknownBarcodes] ([StoreCode],[DateInserted],[StoreTypeID],[Barcode],[UnknownBarcodeStatusID],[StatusDescription],[IssueName],[IssuePrice],[AutoReported]) VALUES (1531,2015-07-15 08:01:03.817,1,'977096171301112',3,'Scanning issues regarding the reported unknown item','ASIAN TRADER FREE TRADE',10,1)

您可以看到，上面集合的MODE应该是10，即使其中一行的值为200，这偏离了AVG

Answer 1

From: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/calculating-mean-median-and-mode-with-sq/

要使用sql server计算模式，我们首先需要获得集合中每个值的计数。然后，我们需要过滤数据，以便返回与计数相等的值。

Declare @Temp Table(Id Int Identity(1,1), Data Decimal(10,5))
Insert into @Temp Values(1)
Insert into @Temp Values(2)
Insert into @Temp Values(5)
Insert into @Temp Values(5)
Insert into @Temp Values(5)
Insert into @Temp Values(6)
Insert into @Temp Values(6)
Insert into @Temp Values(6)
Insert into @Temp Values(7)
Insert into @Temp Values(9)
Insert into @Temp Values(10)
Insert into @Temp Values(NULL)
SELECT TOP 1 with ties DATA
FROM   @Temp
WHERE  DATA IS Not NULL
GROUP  BY DATA
ORDER  BY COUNT(*) DESC

在您的示例中，函数sql可以通过子查询完成:

(select top 1 IssuePrice from UnknownBarcodes barx where barx.BarCode = bar.Barcode group by barx.IssuePrice order by count(*) DESC ) IssuePrice_MODE

查询总数:

declare @Status int, @StoreTypeId int;
set @Status = 3;
set @StoreTypeId = 1;
SELECT 
     COUNT(bar.StoreCode) AS [# Stores]
    ,MAX(bar.IssueName) AS IssueName
    ,AVG(bar.IssuePrice) AS IssuePrice
    ,(select top 1 IssuePrice from UnknownBarcodes barx where barx.BarCode = bar.Barcode group by barx.IssuePrice order by count(*) DESC ) IssuePrice_MODE
    ,bar.Barcode
    ,Product.EAN13 AS [Matched Product Code]
    ,Product.Name AS [Matched Product Name]
    ,Product.MainCatagory AS [Product Catagory]
FROM UnknownBarcodes bar
LEFT JOIN Product on LEFT(bar.Barcode,13) = Product.EAN13
WHERE bar.UnknownBarcodeStatusID = @Status
AND LEN(bar.Barcode) >= 10
AND bar.StoreTypeID = @StoreTypeID
GROUP BY bar.Barcode, Product.EAN13, Product.Name, Product.MainCatagory
ORDER BY
    CASE WHEN @status = 1 THEN
        COUNT(bar.StoreCode)
        WHEN @status = 2 THEN
        COUNT(bar.StoreCode) 
        WHEN @status = 3 THEN
        MAX(bar.DateInserted) 
        end
     DESC

下面的查询可能对大型数据集更有效，因为它限制了必须迭代的结果

declare @Status int, @StoreTypeId int;
set @Status = 3;
set @StoreTypeId = 1;
with FirstQuery as (SELECT 
    *
FROM UnknownBarcodes bar
LEFT JOIN Product on LEFT(bar.Barcode,13) = Product.EAN13
WHERE bar.UnknownBarcodeStatusID = @Status
AND LEN(bar.Barcode) >= 10
AND bar.StoreTypeID = @StoreTypeID
)

select COUNT(StoreCode) AS [# Stores]
    ,MAX(IssueName) AS IssueName
    ,AVG(IssuePrice) AS IssuePrice
    ,Barcode
    ,(select top 1 IssuePrice from FirstQuery barx where barx.BarCode = FirstQuery.Barcode group by barx.IssuePrice order by count(*) DESC ) IssuePrice_MODE
    ,EAN13 AS [Matched Product Code]
    ,Name AS [Matched Product Name]
    ,MainCatagory AS [Product Catagory] 
    from FirstQuery
    GROUP BY Barcode, EAN13, Name, MainCatagory
ORDER BY
    CASE WHEN @status = 1 THEN
        COUNT(StoreCode)
        WHEN @status = 2 THEN
        COUNT(StoreCode)
        WHEN @status = 3 THEN
        MAX(DateInserted)
    END DESC

Answer 2

我使用内部查询来计算MODE

SELECT COUNT(StoreCode) AS [# Stores]
    ,MAX(IssueName) AS IssueName
    ,AVG(IssuePrice) AS IssuePrice,
    (select top 1 issuePrice from [UnknownBarcodes] where ID = UnknownBarcodes.id group by IssuePrice having IssuePrice > 1) issuePrice_MODE
    ,Barcode
    ,Product.EAN13 AS [Matched Product Code]
    ,Product.Name AS [Matched Product Name]
    ,Product.MainCatagory AS [Product Catagory]
FROM UnknownBarcodes 
LEFT JOIN Product on LEFT(UnknownBarcodes.Barcode,13) = Product.EAN13
WHERE UnknownBarcodeStatusID = 3
AND LEN(Barcode) >= 10
AND StoreTypeID = 1
GROUP BY Barcode, Product.EAN13, Product.Name, Product.MainCatagory

Answer 3

如何为您所追求的工作?不知道它在更大的数据集上的表现如何:

;with cte as
(
select count(1) over (partition by b.IssueName
                                            ,p.EAN13
                                            ,p.Name
                                            ,p.MainCatagory
                                            ) as [# Stores]
        ,b.IssueName
        ,b.IssuePrice
        ,row_number() over (partition by b.IssueName
                                            ,p.EAN13
                                            ,p.Name
                                            ,p.MainCatagory
                            order by count(1) desc
                            ) as IssuePriceSort
        ,b.Barcode
        ,p.EAN13
        ,p.Name
        ,p.MainCatagory
from UnknownBarcodes as b
    left join Product as p
        on left(b.Barcode,13) = p.EAN13
where UnknownBarcodeStatusID = @Status
      and len(Barcode) >= 10
    and StoreTypeID = @StoreTypeID
group by b.Barcode
        ,b.IssueName
        ,b.IssuePrice
        ,p.EAN13
        ,p.Name
        ,p.MainCatagory
)
select [# Stores]
    ,[IssueName]
    ,[IssuePrice]
    ,[IssuePriceSort]
    ,[Barcode]
    ,[EAN13]
    ,[Name]
    ,[MainCatagory]
from cte
where IssuePriceSort = 1