时间戳

首页 > 解决方案 > 具有不同和按雪花分组的 SQL 查询

sql - 具有不同和按雪花分组的 SQL 查询

问题描述

我想要完成的是获取给定 MPN 的所有记录，但是，我只想要最新DeliveryDate的， shpm但鉴于该 MAX函数需要在 group by 子句中，它没有获得最新的记录，由于 distinct 获得了所有记录 DeliveryDate，它获得了两条记录而不是一条，我怎么能做到这一点？这是在雪花中。

这是我的 SQL 代码

SELECT
    MD.MPN,
    MD.LOTCODE,
    MD.DATECODE,
    SHIP.ITEMCODE AS SYSTEMPARTNUMBER, 
    SHIP.SERIALNUMBER AS SYSTEMSERIALNUMBER, 
    SHIP.CUSTOMERNAME, 
    SHIP.SHIPTOADDRESS AS ADDRESS,
    SUM(IFNULL(SHIP.QUANTITY,0)) AS QUANTITY,
    SHIP.DELIVERYDATE
FROM cunits UNITS
   JOIN unc UC ON UC.CHILDUNITID = UNITS.ID
   JOIN shpm SHIP ON SHIP.SERIALNUMBER = UC.SYSSN
   JOIN tsern SN ON SN.UNITID = UNITS.ID
   JOIN machined MD ON MD.SERIALNUMBER = SN.SERIALNUMBER     
WHERE --SYSTEMSERIALNUMBER = '001801055469' and 
MPN = 'XC0402A105KP5CNN-S'
GROUP BY MD.MPN,MD.LOTCODE,MD.DATECODE,SHIP.ITEMCODE,SHIP.SERIALNUMBER,SHIP.CUSTOMERNAME,SHIP.SHIPTOADDRESS

标签： sqlgroup-bydistinctsnowflake-cloud-data-platform

解决方案

使用ROW_NUMBER()和QUALIFY：

SELECT MD.MPN, MD.LOTCODE, MD.DATECODE,
       SHIP.ITEMCODE AS SYSTEMPARTNUMBER, SHIP.SERIALNUMBER AS SYSTEMSERIALNUMBER, 
       SHIP.CUSTOMERNAME, SHIP.SHIPTOADDRESS AS ADDRESS,
       SUM(COALESCE(SHIP.QUANTITY, 0)) AS QUANTITY,
       SHIP.DELIVERYDATE
FROM cunits UNITS JOIN
     unc UC
     ON UC.CHILDUNITID = UNITS.ID JOIN
     shpm SHIP
     ON SHIP.SERIALNUMBER = UC.SYSSN JOIN
     tsern SN
     ON SN.UNITID = UNITS.ID JOIN
     machined MD
     ON MD.SERIALNUMBER = SN.SERIALNUMBER     
WHERE '001801055469' and MPN = 'XC0402A105KP5CNN-S'
GROUP BY MD.MPN, MD.LOTCODE, MD.DATECODE, SHIP.ITEMCODE, SHIP.SERIALNUMBER, SHIP.CUSTOMERNAME, SHIP.SHIPTOADDRESS
QUALIFY ROW_NUMBER() OVER (PARTITION BY MD.MPN, SHIP.SERIALNUMBER ORDER BY SHIP.SHIPDATE DESC) = 1;

这将返回 row per MPN，这就是我解释您的问题的方式。您可能还需要其他列PARTITION BY。

推荐阅读