У меня есть сценарий красного смещения
Вот код части скрипта
SELECT cog.organizationid,
cog.clientid,
t.MonthBilled,
t.Month,
t.Year,
u.ClientCharges,
cp.supervisorcontactid AS Principal,
cc.fullname AS CSL,
LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges,
CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month)
END AS Delta,
CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END) > 0 THEN 'Gain'
WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END < 0 THEN 'Loss'
ELSE NULL END AS GainsLosses,
CASE WHEN u.ClientCharges >=0 AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL AND t.MonthBilled <> '1/1/2015' THEN 'New' ELSE 'Existing' END AS ClientType,
CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,
CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS ActiveLastMonth,
CASE WHEN u.ClientCharges IS NULL AND (LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) <> 0 AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,
u2.FeatureKeyCount,
CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter
FROM public.contacts_client_organization cog
У меня появляется это сообщение об ошибке при попытке запустить
[42601][500310] Amazon Invalid operation: syntax error at or near "Delta" Position: 747; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: syntax error at or near "Delta" Position: 747;
Но я не понял, в чем дело?
ОБНОВИТЬ
Вот переписанная часть с действительным LAG, но у меня все еще та же проблема
SELECT cog.organizationid,
cog.clientid,
t.MonthBilled,
t.Month,
t.Year,
u.ClientCharges,
cp.supervisorcontactid AS Principal,
cc.fullname AS CSL,
LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges,
CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month)
END AS Delta,
CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END) > 0 THEN 'Gain'
WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END < 0 THEN 'Loss'
ELSE NULL END AS GainsLosses,
CASE WHEN u.ClientCharges >=0 AND LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL AND t.MonthBilled <> '1/1/2015' THEN 'New' ELSE 'Existing' END AS ClientType,
CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,
CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS ActiveLastMonth,
CASE WHEN u.ClientCharges IS NULL AND (LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) <> 0 AND LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,
u2.FeatureKeyCount,
CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter
FROM public.contacts_client_organization cog
Я не уверен, что это проблема, но lag()
не принимает третий аргумент для значения по умолчанию в Redshift.
Я бы написал это так:
(COALESCE(u.ClientCharges, 0) -
COALESCE(LAG(u.ClientCharges, 1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid, t.Year, t.Month), 0)
) as my_Delta,
DELTA
- это Зарезервированное слово Amazon Redshift, поэтому дайте ему другое имя.
Что такое COALESCE? Я удаляю 3 аргумента, и эта ошибка все еще остается
Это стандартная версия NVL()
ANSI / ISO: docs.aws.amazon.com/redshift/latest/dg/r_NVL_function.html.
Я думаю, у вас ошибка около ,0
в этой строке COALESCE(CASE WHEN LAG(u.ClientCharges, 1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid, t.Year, t.Month), 0)
@EugeneSukh. . . В экспрессии вообще не должно было быть CASE
.
Да, но он все еще показывает мне эту ошибку. Мех. Я имею в виду синтаксическую ошибку рядом с "Дельтой"
Это предположение, но я бы попробовал добавить партезы:
((CASE ... END) - (CASE ... END)) as Delta
. Так быть не должно. но может это сбивает с толку. В любом случае,COALESCE
лучше при проверке наNULL
.