Notre ensemble de données associe fondamentalement un ensemble de dates (des semaines de la semaine en cours au passé) à un ensemble de sections selon que ces sections ont commencé ou avant et se sont terminées le ou après cette semaine. Alors qu'à l'origine, cette requête nous a donné les résultats attendus, cette semaine, elle a commencé à nous fournir des résultats incorrects. Après un tas de bricolages, nous avons découvert que si nous changions la requête en LEFT JOIN puis filtrions la requête en utilisant une clause WHERE , cela nous donnait à nouveau des résultats corrects. Quelle est la différence? Pourquoi l'un fonctionne et l'autre pas? ( Points bonus: pourquoi la requête d'origine a-t-elle fonctionné pendant des semaines avant de rencontrer soudainement cette erreur?) Effectuer la même jointure interne sur Redshift donne des résultats corrects, il semble donc être une nuance de flocon de neige que nous ne faisons pas comprendre.
Requête d'origine:
WITH week_list AS
(
SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
UNION ALL
SELECT DATEADD(week, 1, week_value)
FROM week_list
WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
SELECT wl.week_value, s.id section_id
FROM week_list wl
LEFT JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
WHERE s.id IS NOT NULL
)
SELECT aspw.week_value, COUNT(DISTINCT aspw.section_id) count_sections
FROM active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC
Résultats: Une ligne, datée du 2019-12 -30 (il y a 4 semaines). Aucune donnée pour les trois dernières semaines.
Remarque: Si vous ajustez le DATEADD dans le premier CTE, quelle que soit la première date renvoyée, la connexion semblera toujours réussir. Ce comportement n'a commencé que la semaine dernière - auparavant, cette requête fournissait le nombre de lignes attendu (en d'autres termes, le nombre de semaines spécifié dans ce premier DATEADD).
Requête "fixe":
WITH week_list AS
(
SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
UNION ALL
SELECT DATEADD(week, 1, week_value)
FROM week_list
WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
SELECT
wl.week_value, s.id section_id
FROM week_list wl
JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
)
SELECT
aspw.week_value,
COUNT(DISTINCT aspw.section_id) count_sections
FROM
active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC
Résultats: renvoie quatre lignes, des semaines du 30/12/2019 au 2020- 01-20, avec le nombre de sections approprié.
3 Réponses :
Ceci est un CTE récursif sur "week_list". Redshift ne prend pas en charge les CTE récursifs .
Snowflake prend en charge les CTE récursifs , ce qui expliquerait la différence de comportement.
Il est difficile de tester cela sans les données sous-jacentes. Si vous obtenez des résultats corrects dans Redshift, il y a de fortes chances que vous n'ayez pas besoin ou que vous ne souhaitiez pas un CTE récursif. Vous pouvez le modifier pour que "week_list" ne se réfère pas à lui-même.
Quant à savoir pourquoi cela fonctionnait auparavant, il est probable que l'état de la table et le CTE récursif ne fonctionnaient que dans des cas particuliers. Lorsque CURRENT_DATE () a avancé, il l'a sorti de ce cas particulier. De plus, la jointure interne et la jointure externe gauche où s.id N'EST PAS NULL would seraient équivalentes si ce n'est dans un CTE récursif.
Vous pouvez en savoir plus sur les CTE récursifs ici:
Merci pour les notes! Je m'excuse de ne pas avoir été plus clair dans mon article d'origine - bien que cette requête "fonctionne" dans Redshift, elle nécessite beaucoup de petits changements syntaxiques ainsi qu'un changement majeur: le CTE doit être modifié afin de créer une liste de dates ( ce qui, comme vous l'avez dit, est différent puisque Redshift ne supporte pas ce type de récursivité). Cette partie de la requête que j'ai modifiée pour être quelque chose comme: SELECT DATEADD ('week', -n, DATE_TRUNC ('week', CURRENT_DATE)) AS week_value FROM (SELECT ROW_NUMBER () OVER () AS n FROM stl_scan LIMIT 4) n ORDER BY week_value DESC
Je vais vérifier le lien que vous avez fourni (merci!) Mais avez-vous des raisons de penser que c'est le CTE récursif qui est à l'origine du problème? J'adorerais vraiment comprendre le problème car il y a d'autres jointures internes impliquées dans cette requête (elle est beaucoup plus longue et plus complexe que l'extrait de code fourni, l'extrait va juste au cœur de ce qui est cassé) et je crains que d'autres jointures pourraient soudainement "casser" parce que je n'ai pas compris quel était le problème en premier lieu.
Salut Vic. Sans les données sous-jacentes pour le tester, la récursivité sur le CTE est une estimation éclairée de la raison pour laquelle il se comporte différemment avec une jointure interne par rapport à une jointure gauche avec la clause where. En lisant certains des commentaires, il semble que vous ayez un dossier ouvert avec le support. Je pense que c'est une excellente idée de les faire jeter un coup d'œil et d'avoir accès à l'ensemble de la situation peut prendre une décision.
le CTE récursif peut être évité si les -4 semaines est une constante avec ce code:
WITH week_list AS (
SELECT DATEADD(week, column1, DATE_TRUNC(week, CURRENT_DATE()))
FROM VALUES (-4),(-3),(-2),(-1),(0)
)
avec le flocon de neige JOIN déplacera les filtres plus haut dans la pile d'exécution, et vous pourriez avoir trouvé un bogue. Où-comme avec le LEFT JOIN (même s'il a une clause WHERE équivalente, il évite probablement l'optimisation cassée agressive.
Il y avait un logiciel publié hier soir pour nous, mais nous sommes sur un compte Enterprise, donc vous avez peut-être été mis à jour 2 jours auparavant. Cette version avait un certain nombre de bogues qui nous ont impactés, nous l'avons fait revenir (pour nous)
p >
Fascinant! Je suppose que je dois rester au courant des versions - je laisse généralement cela à mon équipe d'ingénierie des données et à nos représentants Snowflake. C'est super utile, je ferai un suivi avec ce groupe pour voir s'il s'agit bien d'un bug. Merci beaucoup!
Eh bien, si votre processus n'a pas changé et qu'il n'y a pas de différence dans les données, la seule option qui reste est que le moteur a changé et qu'ils sortent fréquemment, et quelques fois par an, les versions ne sont au moins "pas bonnes pour notre cas d'utilisation "et d'autres choses du temps deviennent plus rapides comme par magie ..
Merci, ça a du sens! Je viens également de publier une réponse inspirée de votre suggestion de se débarrasser du CTE récursif. Cela semble avoir fait l'affaire! Merci encore!
Merci pour tous vos commentaires! La bonne nouvelle est que vous m'avez tous aidé à trouver une solution dont je pense être satisfaite. J'ai également suivi Snowflake afin qu'ils puissent enquêter sur ce comportement et voir s'il s'agissait d'une erreur utilisateur de ma part parce que je ne comprenais pas comment les CTE récursifs traitent, ou s'il s'agit d'un bogue introduit dans une version récente.
Voici ce que J'ai trouvé: alors que la récursivité fonctionne pour le cas d'utilisation auquel je l'appliquais (génération d'une liste de dates basée sur CURRENT_DATE ), ce n'est pas strictement nécessaire. Puisque nous voulons une liste de dates, je pourrais tout aussi facilement générer un tableau et utiliser les numéros de ligne pour effectuer les ajustements DATEADD .
Cela ressemble à ceci:
SELECT DATEADD(week, '-' || ROW_NUMBER() OVER (ORDER BY NULL),
DATEADD(week, 1, DATE_TRUNC(week, CURRENT_DATE()))) AS week_value
FROM table (generator(rowcount => 200))
L'un des grands avantages de cette approche est que je ne suis plus limité par le paramètre MAX_RECURSIONS dans Snowflake (qui est défini sur 100 par défaut). Puisque j'utilise ces données pour créer des graphiques d'activité au fil du temps, avoir 200 valeurs me donne plus de trois ans d'histoire plutôt que juste moins de 2 ans d'histoire. Je n'ai pas non plus besoin de contacter mon représentant Snowflake si je veux le développer.
Changer la week_list CTE en cette approche non récursive semble résoudre le problème à l'origine du problème. INNER JOIN pour ne pas fonctionner correctement. Nous ne comprenons toujours pas pourquoi le CTE récursif a semblé fonctionner pendant plusieurs semaines puis a soudainement commencé à mal se comporter, mais si Snowflake peut faire la lumière sur cela via notre ticket de support, je reviendrai ici pour fournir une mise à jour. Merci à tous pour votre aide et vos conseils!
Ah, c'est marrant que vous ayez utilisé un générateur, car c'est ce que nous utilisons, mais je l'ai simplifié, comme votre exemple en avait 4, et les valeurs constantes fonctionnent tout aussi bien là-bas .., mais super de voir que vous avez trouvé une solution plus utile pour vous .
C'est probablement une question idiote, mais avez-vous une table appelée
week_listainsi qu'un CTE avec le même nom?Juste pour vérifier si la fusion de vues se produit, que se passerait-il dans la requête d'origine si vous ajoutiez "WHERE 1 = 1" dans active_sections_per_week
Bonne question @MikeWalton! Je n'ai pas de table séparée avec le même nom,
week_listici fait toujours référence au CTE.Merci @GeorgeJoseph pour la suggestion! J'ai essayé d'ajouter
WHERE 1 = 1dans laactive_sections_per_weekCTE, mais les résultats sont les mêmes (une seule semaine est retournée).pas de chance si vous essayez WHERE CURRENT_DATE () = CURRENT_DATE ()
Merci pour la suggestion - malheureusement, pas de chance!