时间戳

首页 > 解决方案 > Microsoft SQL Server：选择每台机器上生产的每个零件号的前 N ​​个、联合和反透视

sql-server - Microsoft SQL Server：选择每台机器上生产的每个零件号的前 N ​​个、联合和反透视

问题描述

我有两台机器，每台机器都生产相同的零件号。每台机器都有三个参数。数据存储在一行中。我想根据降序时间从每台机器上生产的每个零件编号中选择前 N 个结果（即机器 1 和 2 上生产的零件 1、2、3 和 4 的最新 5 个结果），取消旋转结果并显示他们每个都有一个新的行号。环境是 Microsoft SQL Server 2016。

样本数据：

id  PartNumber  WorkCenter  UniquePartID    Measurement1    Measurement2 Measurement3 transactionDT
--------------------------------------------------------------------------------------------------------------
1   1           1           78              24.2            20.6         24.6         2018-08-10 11:03:34.000
2   1           2           135             24.3            20.7         24.7         2018-08-10 11:03:28.000
3   2           2           45              58.7            49.9         59.6         2018-08-09 11:03:22.000
4   2           2           32              58.6            49.8         59.5         2018-08-09 11:03:16.000
5   3           1           65              42.2            35.9         42.8         2018-08-07 11:03:10.000
6   3           2           96              42.2            35.9         42.8         2018-08-07 11:03:04.000
7   4           1           12             108.2            92          109.8         2018-08-06 11:02:57.000
8   4           2           54             108.4            92.1        110.2         2018-08-06 11:02:50.000
9   1           1           77              24.2            20.6         24.6         2018-08-05 11:02:43.000
10  1           1           76              24.2            20.6         24.6         2018-08-05 11:02:36.000

所需结果的前几行：

ROWID   PARTNUMBER  TIMESTAMP               WORKCENTER  UniquePartID    PARAMETER   MEASUREMENT
------------------------------------------------------------------------------------------------
1       1           2018-08-10 11:03:34.000 1            78       Measurement 1     24.2
2       1           2018-08-10 11:03:34.000 1            78       Measurement 2     20.6
3       1           2018-08-10 11:03:34.000 1            78       Measurement 3     24.6
4       1           2018-08-10 11:03:28.000 2           135       Measurement 1     24.3
5       1           2018-08-05 11:02:43.000 2           135       Measurement 2     20.7
6       1           2018-08-05 11:02:36.000 2           135       Measurement 3     24.7

使用的 T-SQL 语句：

SELECT 
    ROW_NUMBER() OVER (ORDER BY TIMESTAMP DESC) ROWID, 
    *
FROM
    (SELECT DISTINCT TOP 10 
         PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, 
         CASE 
            WHEN WorkCenter = 1 THEN 'WC 1' 
            WHEN WorkCenter = 2 THEN 'WC 2' 
         END AS WORKCENTER, 
         UniquePartID, 'Measurement1' AS PARAMETER, 
         measurement1 AS MEASUREMENT
     FROM            
         parts.dbo.data 

     UNION

     SELECT DISTCINT TOP 10 
         PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, 
         CASE 
            WHEN WorkCenter = 1 THEN 'WC 1' 
            WHEN WorkCenter = 2 THEN 'WC 2'  
         END AS WORKCENTER, 
         UniquePartID, 'Measurement2' AS PARAMETER, 
         measurement2 AS MEASUREMENT
     FROM           
         parts.dbo.data

     UNION

     SELECT DISTCINT TOP 10 
         PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, 
         CASE 
            WHEN WorkCenter = 1 THEN 'WC 1' 
            WHEN WorkCenter = 2 THEN 'WC 2'  
         END AS WORKCENTER, 
         UniquePartID, 'Measurement3' AS PARAMETER, 
         measurement3 AS MEASUREMENT
     FROM            
         parts.dbo.data) a
ORDER BY  
    TIMESTAMP DESC, PARAMETER ASC

谢谢！

******更新的 t-SQL 代码

SELECT ROW_NUMBER() OVER(PARTITION BY PARTNUMBER, WORKCENTER ORDER BY 
    TIMESTAMP DESC) ROWID, *
FROM (

SELECT PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, CASE 
  WHEN WorkCenter = 1 THEN 'Work Center 1' WHEN WorkCenter = 2 THEN 'Work 
    Center 2' END AS WORKCENTER, UniquePartID, 'Measurement1' AS PARAMETER, 
                     Measurement1 AS MEASUREMENT, Measurement1Min AS 
    SPEC_MIN, Measurement1Nominal AS NOMINAL, Measurement1Max AS SPEC_MAX
FROM            parts.dbo.data

UNION ALL

SELECT PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, CASE 
    WHEN WorkCenter = 1 THEN 'Work Center 1' WHEN WorkCenter = 2 THEN 'Work 
    Center 2' END AS WORKCENTER, UniquePartID, 'Measurement2' AS PARAMETER, 
                     Measurement2 AS MEASUREMENT, Measurement2Min AS 
    SPEC_MIN, Measurement2Nominal AS NOMINAL, Measurement2Max AS SPEC_MAX
FROM            parts.dbo.data

UNION ALL

SELECT PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, CASE WHEN 
    WorkCenter = 1 THEN 'Work Center 1' WHEN WorkCenter = 2 THEN 'Work 
    Center 2' END AS WORKCENTER, UniquePartID, 'Measurement3' AS PARAMETER, 
                     Measurement3 AS MEASUREMENT, Measurement3Min AS 
    SPEC_MIN, Measurement3Nominal AS NOMINAL, Measurement3Max AS SPEC_MAX
FROM            parts.dbo.data) a
order by TIMESTAMP DESC, PARAMETER ASC

挑战 - 这将为每条记录返回 3 行...我希望每个零件号/工作中心组合的前 N ​​个为 3 行。

标签： sql-serverdistinctunion

解决方案

您的语句的问题是您在查询TOP 10的每个部分都使用UNION而不进行排序。

我猜你想要这样的东西：

SELECT ROW_NUMBER() OVER (ORDER BY transactionDT DESC, MEASUREMENT) ROWID,
  PartNumber AS PARTNUMBER, transactionDT AS TIMESTAMP, WorkCenter AS WORKCENTER,
  UniquePartID, PARAMETER, MEASUREMENT
FROM 
   (SELECT TOP (10) PartNumber, WorkCenter, UniquePartID,
      transactionDT, Measurement1, Measurement2, Measurement3
    FROM parts.dbo.data
    ORDER BY transactionDT DESC) AS pvt
UNPIVOT (MEASUREMENT FOR PARAMETER IN (Measurement1, Measurement2, Measurement3)) AS upvt;

更新...

在将要求扩展到 4 列而不是 1 列之后，我似乎找不到与使用该UNION ALL方法不同的方法来解决这个问题。与TOP N我对原始问题的回答一样，ROW_NUMBER()我对扩展要求的解决方案中的编号必须“尽早”完成，在这种情况下，这意味着它必须已经在UNION查询的每个单独部分中进行。将在最后TOP 10使用一个WHERE子句找到：

WITH 
  NumberedRecords (ROWID, PARTNUMBER, TIMESTAMP, WORKCENTER, UniquePartID, PARAMETER, MEASUREMENT, SPEC_MIN, NOMINAL, SPEC_MAX) AS (

    SELECT 
      ROW_NUMBER() OVER(PARTITION BY PartNumber, WorkCenter ORDER BY transactionDT DESC)
      , PartNumber, transactionDT, 'Work Center ' + CAST(WorkCenter AS varchar(10)), UniquePartID
      , 'Measurement 1', Measurement1, Measurement1Min, Measurement1Nominal, Measurement1Max
    FROM parts.dbo.data

    UNION ALL

    SELECT 
      ROW_NUMBER() OVER(PARTITION BY PartNumber, WorkCenter ORDER BY transactionDT DESC)
      , PartNumber, transactionDT, 'Work Center ' + CAST(WorkCenter AS varchar(10)), UniquePartID
      , 'Measurement 2', Measurement2, Measurement2Min, Measurement2Nominal, Measurement2Max
    FROM parts.dbo.data

    UNION ALL

    SELECT 
      ROW_NUMBER() OVER(PARTITION BY PartNumber, WorkCenter ORDER BY transactionDT DESC)
      , PartNumber, transactionDT, 'Work Center ' + CAST(WorkCenter AS varchar(10)), UniquePartID
      , 'Measurement 3', Measurement3, Measurement3Min, Measurement3Nominal, Measurement3Max
    FROM parts.dbo.data

  )
SELECT * FROM NumberedRecords WHERE ROWID <= 10;

简单的方法：

现在知道这将是一个带有 的查询UNION，有一个简单的解决方案，只需对您已经执行的操作进行少量更改，这将适用于您的原始查询和更新后的查询：

在外部查询中OVER的函数子句中，添加用于分区的字段。ROW_NUMBERPARAMETER

当然，WHERE最后使用一个子句：

WHERE ROWID <= 10

推荐阅读