如何在 Google BigQuery SQL 中查找特定列的最小值，当左边连接到另一个表时?

Question

我正在努力在事务级别上拉回最小值和最大值。我正在尝试做的是通过左连接将一种类型的事务与另一种类型的事务配对。但是，由于有多个事务可以与我的异常填充匹配，因此我正在提取许多重复项。我试图遵循现有问题并实施这些技巧，但仍然没有成功。

这是我的查询示例：

SELECT *
FROM ( Select DISTINCT 
B.POS_TRANS_ID,
B.SLS_DT,
(MAX(B.SLS_DT) < H.BUS_DT) AS MAX_SLS_DT,
(MIN(DATE_DIFF(H.BUS_DT,B.SLS_DT,DAY))) AS MIN_DAY_DIFF, 
B.NET_EXT_RETL_AMT,
H.STR_NBR,
H.MKUP_MKDN_CTRL_NBR,
H.MKDN_RSN_TXT,
H.BUS_DT,
H.CURR_RETL_AMT,
H.TRANS_ID
FROM `EXAMPLE_TABLE` H
left join `miami` B
on H.SKU_NBR = B.SKU_NBR
and H.STR_NBR = B.STR_NBR
and (H.UNT_CNT*-1) = B.QTY_SOLD
and (H.NET_MUMD_AMT*-1) = (B.CURR_RETL_AMT)
where 
B.QTY_SOLD < 0
and cast(MKUP_MKDN_CTRL_NBR as string) not like ('99%')
AND H.TRANS_ID IN ('FLORIDA_TRANS')

GROUP BY 
B.POS_TRANS_ID,
B.SLS_DT, 
B.NET_EXT_RETL_AMT,
H.STR_NBR,
H.MKUP_MKDN_CTRL_NBR,
H.MKDN_RSN_TXT,
H.BUS_DT,
H.CURR_RETL_AMT,
H.TRANS_ID)Z
WHERE MAX_SLS_DT IS TRUE
AND Z.MKUP_MKDN_CTRL_NBR = 69308
AND Z.STR_NBR = '3204'

这是我的结果示例：

POS_TRANS_ID   SLS_DT    MAX_SLS_DT    MIN_DAY_DIFF    NET_EXT_RETL_AMT    STR_NBR MKUP_MKDN_CTRL_NBR
4768    10/4/2017   TRUE            566                 -99           3204       69308
5441    1/3/2017    TRUE            840                 -99           3204       69308
8824    4/25/2018   TRUE            363                 -99           3204       69308
8870    3/10/2018   TRUE            409                 -99           3204       69308

当我实际上只想通过商店交易进行最小日期差异和max_sls_dt时......

期望的结果：

POS_TRANS_ID   SLS_DT    MAX_SLS_DT    MIN_DAY_DIFF    NET_EXT_RETL_AMT    STR_NBR MKUP_MKDN_CTRL_NBR
8824    4/25/2018   TRUE            363                 -99           3204       69308

我仍然在学习 SQL，所以任何帮助将不胜感激。提前谢谢你！

Answer 1

ROW_NUMBER在这里是一个选项：

WITH cte AS (
SELECT
B.POS_TRANS_ID,
B.SLS_DT,
MAX(B.SLS_DT) < H.BUS_DT AS MAX_SLS_DT,
MIN(DATE_DIFF(H.BUS_DT, B.SLS_DT, DAY)) AS MIN_DAY_DIFF, 
B.NET_EXT_RETL_AMT,
H.STR_NBR,
H.MKUP_MKDN_CTRL_NBR,
H.MKDN_RSN_TXT,
H.BUS_DT,
H.CURR_RETL_AMT,
H.TRANS_ID,
ROW_NUMBER() OVER (PARTITION BY H.STR_NBR
ORDER BY MIN(DATE_DIFF(H.BUS_DT, B.SLS_DT, DAY))) rn
FROM EXAMPLE_TABLE H
LEFT JOIN miami B
ON H.SKU_NBR = B.SKU_NBR AND
H.STR_NBR = B.STR_NBR AND
H.UNT_CNT*-1 = B.QTY_SOLD AND
H.NET_MUMD_AMT*-1 = B.CURR_RETL_AMT AND
MKUP_MKDN_CTRL_NBR = 69308
WHERE
B.QTY_SOLD < 0 AND
CAST(MKUP_MKDN_CTRL_NBR AS string) NOT LIKE '99%' AND
H.TRANS_ID = 'FLORIDA_TRANS'
GROUP BY 
B.POS_TRANS_ID,
B.SLS_DT, 
B.NET_EXT_RETL_AMT,
H.STR_NBR,
H.MKUP_MKDN_CTRL_NBR,
H.MKDN_RSN_TXT,
H.BUS_DT,
H.CURR_RETL_AMT,
H.TRANS_ID
HAVING
MAX(B.SLS_DT) < H.BUS_DT IS TRUE
)
SELECT *
FROM cte
WHERE rn = 1;