时间戳

首页 > 解决方案 > 通过比较子详细信息查找公共数据集

sql - 通过比较子详细信息查找公共数据集

问题描述

我有一张表格，其中详细说明了销售细节、产品和数量以及其他一些细节，如下所示。

SalesOrderID    ProductID   OrderQty
           1          762          1
           1          758          1
           2          760          1
           3          710          3
           3          773          1
           3          778          1
           3          775          1
           4          747          1
           5          710          1
           5          709          2
           5          773          2
           5          776          1
           6          762          1
           6          758          1

我正在尝试mark / group销售相同的订单，即它们具有相同的ProductID列表OrderQty。例如，在上述情况下，SalesOrderID与1和6例如是相同的。

到目前为止，我已经尝试通过ProductID&的组合对数据进行分组OrderQty，然后尝试 sum over SalesOrderID，但显然这不是一个可靠的解决方案。

With TempView as (
  Select SalesOrderID, ProductID, OrderQty,
    DENSE_RANK() OVER(ORDER BY ProductID, OrderQty) as 'RANK_P_W_Q'
    FROM SalesRecords
), SumBySalesID as (
  Select SalesOrderID, ProductID, OrderQty, SUM(RANK_P_W_Q)
    OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID 
     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'SalesMarker'
  FROM TempView
) Select SalesOrderID, ProductID, OrderQty,
    DENSE_RANK() OVER(ORDER BY SalesMarker) as 'SalesGroup'
    FROM SumBySalesID

下一个解决方案是旋转表格ProductID，然后将它直接分组SalesOrderID，但是，我不能依赖这种策略，因为，Pivot 通常会导致多于1024列，这是正常的限制表。我知道，wide-tables但我不想走那条路，因为我必须管理沿行的数据长度，8,096 Bytes并且我想让脚本尽可能简单。

我希望有人能指出我正确的方向。

标签： sqltsqlgroup-bypivotgrouping

解决方案

在 MSSQL 2017 中只使用STRING_AGG函数，2016在这里查看如何模拟它。

SQL 演示

首先为每个带有产品和数量的订单创建一个键。

SELECT [SalesOrderID]
     , STRING_AGG (CAST([ProductID] as nvarchar(100))  
                 + '-' 
                 + CAST([OrderQty] as nvarchar(100)), ',')  
                 WITHIN GROUP (ORDER BY [ProductID] ASC) AS csv  
FROM Sales
GROUP BY [SalesOrderID];

输出

| SalesOrderID |                     csv |
|--------------|-------------------------|
|            1 |             762-1,758-1 |
|            2 |                   760-1 |
|            3 | 710-3,773-1,778-1,775-1 |
|            4 |                   747-1 |
|            5 | 710-1,709-2,773-2,776-1 |
|            6 |             762-1,758-1 |

然后将每个键组合在一起以查找重复项：

with cte as (
  SELECT [SalesOrderID]
       , STRING_AGG (CAST([ProductID] as nvarchar(100))  
                   + '-' 
                   + CAST([OrderQty] as nvarchar(100)), ',')  
                   WITHIN GROUP (ORDER BY [ProductID] ASC) AS csv
  FROM Sales
  GROUP BY [SalesOrderID]
) 
SELECT *, rank() over (ORDER BY csv) as grp
FROM CTE

输出 

| SalesOrderID |                     csv | grp |
|--------------|-------------------------|-----|
|            5 | 710-1,709-2,773-2,776-1 |   1 |
|            3 | 710-3,773-1,778-1,775-1 |   2 |
|            4 |                   747-1 |   3 |
|            2 |                   760-1 |   4 |
|            1 |             762-1,758-1 |   5 |
|            6 |             762-1,758-1 |   5 |

推荐阅读