#sql #join #google-bigquery #case #union
#sql #Присоединиться #google-bigquery #случай #объединение
Вопрос:
Я пытаюсь создать таблицу, обобщающую данные о продажах по неделям. В нем я пытаюсь, чтобы в одном из соседних столбцов отображались данные о продажах за ту же финансовую неделю в течение предыдущего года (который, согласно финансовому календарю моей организации, имел 53-ю неделю в прошлом году).). Мне также нужно сравнить (количество единиц / количество продаж) с периодом 52 недели назад, который является совершенно другой финансовой неделей (например, неделя 9 2019 года по сравнению с неделей 10 2018).).
Я пробовал использовать как объединения, так и полные внешние объединения, но, учитывая, как выглядят мои данные, они неэффективны (поскольку это еженедельные данные, объединения оказались неэффективными, поскольку мне нужно было исключить информацию о дате из первоначального запроса, а затем обновить столбцы в моей таблице, чтобы отразить неделюданные предназначены для. Очевидно, что это связано с возможностью ошибки, но также требует много времени для выполнения 105 раз), или просто не сработало (попытка полного внешнего соединения возвращала неправильные ответы для всех столбцов). Я также пытался использовать CTE, и это тоже не работает для меня. В настоящее время я пытаюсь использовать оператор CASE, но он также возвращает нулевое значение. Я не совсем уверен, куда идти дальше
#STANDARDSQL
SELECT
DTL.SKU_NBR AS SKU_NBR
, SLS.STR_NBR AS STR_NBR
, CONCAT(TRIM(CAST(SKU_HIER.SKU_NBR AS STRING)), ' ', '-', ' ', TRIM(SKU_HIER.SKU_DESC)) AS SKU
, CONCAT(TRIM(CAST(SKU_HIER.EXT_SUB_CLASS_NBR AS STRING)), ' ', '-', ' ', TRIM(SKU_HIER.SUB_CLASS_DESC)) AS SUB_CLASS
, CONCAT(TRIM(CAST(SKU_HIER.EXT_SUB_SC_NBR AS STRING)), ' ', '-', ' ', TRIM(SKU_HIER.SUB_SC_DESC)) AS SUB_SUB_CLASS
, LOCATION.MKT_NM AS MARKET_NAME
, LOCATION.RGN_NM AS REGION_NAME
, LOCATION.DIV_NM AS DIVISION_NAME
, LOCATION.DIV_NBR AS DIVISION_NUMBER
, LOCATION.RGN_NBR AS REGION_NUMBER
, LOCATION.MKT_NBR AS MARKET_NUMBER
, COMP.STR_COMP_IND AS COMP_IND
, COMP.PY_STR_COMP_IND AS PRIOR_COMP_IND
, CALENDAR.FSCL_WK_DESC AS FISCAL_WEEK
, CALENDAR.FSCL_PRD_DESC AS FISCAL_PERIOD
, CALENDAR.FSCL_WK_END_DT AS END_DATE
, CALENDAR.FSCL_WK_BGN_DT AS BEGIN_DATE
, CALENDAR.FSCL_YR AS FISCAL_YEAR_NBR
, CALENDAR.FSCL_WK_NBR AS WEEK_NUMBER
, CALENDAR.FSCL_YR_WK_KEY_VAL AS FISCAL_KEY
, CALENDAR.LY_FYR_WK_KEY_VAL AS LY_FISCAL_KEY
, SUM(COALESCE(DTL.UNT_SLS,0)) AS UNITS
, SUM(COALESCE(DTL.EXT_RETL_AMT,0) COALESCE(DTL.TOT_GDISC_DTL_AMT,0))
AS SALES
, SUM(CASE
WHEN 1=1 THEN (COALESCE(DTL.EXT_RETL_AMT,0) COALESCE(DTL.TOT_GDISC_DTL_AMT,0)) * COMP.STR_COMP_IND
ELSE 0 END) AS COMP_SALES
, SUM(CASE
WHEN 1=1 THEN (COALESCE(DTL.UNT_SLS,0)) * COMP.STR_COMP_IND
ELSE 0 END) AS COMP_UNITS
, SUM(CASE
WHEN 1=1 AND SLS.SLS_DT = DATE_SUB(SLS.SLS_DT, INTERVAL 364 DAY)
THEN (COALESCE(DTL.EXT_RETL_AMT,0)
COALESCE(DTL.TOT_GDISC_DTL_AMT,0)) * COMP.PY_STR_COMP_IND
ELSE NULL END)
AS LY_COMP_SALES
, SUM(CASE
WHEN 1=1 AND SLS.SLS_DT = DATE_SUB(SLS.SLS_DT, INTERVAL 364 DAY)
THEN (COALESCE(DTL.UNT_SLS,0)) * COMP.PY_STR_COMP_IND
ELSE NULL END)
AS LY_COMP_UNITS
, SUM(CASE
WHEN SLS.SLS_DT = DATE_SUB(SLS.SLS_DT, INTERVAL 371 DAY)
THEN (COALESCE(DTL.EXT_RETL_AMT,0)
COALESCE(DTL.TOT_GDISC_DTL_AMT,0))
ELSE NULL END)
AS LY_SALES
, SUM(CASE
WHEN SLS.SLS_DT = DATE_SUB(SLS.SLS_DT, INTERVAL 371 DAY)
THEN (COALESCE(DTL.UNT_SLS,0))
ELSE NULL END)
AS LY_UNITS
FROM `pr-edw-views.SLS.POS_SLS_TRANS_DTL` AS SLS
INNER JOIN
UNNEST (SLS.DTL) AS DTL
JOIN `pr-edw-views.SHARED.MVNDR_HIER` AS MVNDR
ON DTL.DERIV_MVNDR.MVNDR_NBR = MVNDR.MVNDR_NBR
JOIN `pr-edw-views.SHARED.SKU_HIER_FD` AS SKU_HIER
ON SKU_HIER.SKU_NBR = DTL.SKU_NBR
AND SKU_HIER.SKU_CRT_DT = DTL.SKU_CRT_DT
JOIN `pr-edw-views.SHARED.LOC_HIER_FD` AS LOCATION
ON LOCATION.LOC_NBR = SLS.STR_NBR
JOIN `pr-edw-views.SHARED.CAL_PRD_HIER_FD` AS CALENDAR
ON CALENDAR.CAL_DT = SLS_DT
JOIN `pr-edw-views.SLS.STR_COMP_DAY` AS COMP
ON COMP.CAL_DT = CALENDAR.CAL_DT
AND COMP.STR_NBR = SLS.STR_NBR
WHERE CALENDAR.FSCL_WK_END_DT BETWEEN '2018-01-29' AND '2019-04-07'
AND SLS.SLS_DT BETWEEN '2018-01-29' AND '2019-04-07'
AND POS_TRANS_TYP_CD in ('S', 'R')
AND SKU_HIER.EXT_CLASS_NBR = '025-004'
AND MVNDR.MVNDR_NBR IN (74798, 60002238, 73059, 206820, 76009, 40263, 12879, 76722, 10830, 206823, 87752, 60052261, 70401, 51415, 51414)
AND SKU_HIER.LATEST_SKU_CRT_DT_FLG = TRUE
GROUP BY
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
В настоящее время я получаю нулевые значения в своих LY_SALES
LY_UNITS
LY_COMP_SALES
столбцах , , LY_COMP_UNITS
и, хотя я знаю, что должны были быть местоположения с продажами этих товаров за тот же период предыдущего года. То, к чему я пытаюсь добраться, — это отображение значений предыдущего года рядом со значениями текущего года. Любая помощь будет очень признательна!
Спасибо!
Ответ №1:
Такое условие никогда не может быть выполнено : SLS.SLS_DT = DATE_SUB(SLS.SLS_DT, ИНТЕРВАЛ 371 ДЕНЬ). Просто потому, что SLS_DT не равно SLS_DT-371.
Вы можете предварительно агрегировать таблицу в CTE (добавив SLS_DT в группу по столбцам), а затем заменить РЕГИСТР соединением с предварительно агрегированной таблицей. Цельтесь во что-то вроде этого: и это станет чем-то вроде (обратите внимание — в регистре нет суммы):
CASE WHEN AGGSLS.SLS_DT = DATE_SUB(SLS.SLS_DT, INTERVAL 371 DAY)
THEN (COALESCE(AGGSLS.SUM_EXT_RETL_AMT,0)
COALESCE(AGGSLS.SUM_TOT_GDISC_DTL_AMT,0))
ELSE NULL END
Ответ №2:
Две вещи: 1) WHEN 1=1
может быть выражено просто как WHEN TRUE
, таким образом, легче перемещать операторы, не нарушая И / ИЛИ цепочки
2) чтобы получить данные о продажах за прошлый год. Вы можете либо исключить год из окончательного запроса и ограничить вывод предложением where, либо создать таблицу меньшего размера, содержащую продажи за этот год, продажи за прошлый год за неделю.
По моему скромному мнению, продажи за прошлый год для weeknum — лучший вариант, поскольку вы можете использовать его в другом месте. Но это очень похоже на то, что вы написали
Это будет выглядеть примерно так:
SELECT CALENDAR.FSCL_WK_DESC as week_num,
sum(case when year = year(current_date()) then (COALESCE(DTL.UNT_SLS,0)) * COMP.STR_COMP_IND else 0 end) as this_year
sum(case when year = year(current_date())-1 then (COALESCE(DTL.UNT_SLS,0)) * COMP.STR_COMP_IND else 0 end) as last_year
А затем вы присоединяетесь к исходной таблице, используя week_num
Надеюсь, вы найдете это полезным
Приветствия!