Início / user-23890763

Paul's questions

Martin Hope
Paul
Asked: 2025-03-07 17:16:12 +0800 CST
  • 5

Tenho tentado criar uma análise de recursos para nosso aplicativo contando atributos específicos de usuários de tabelas de banco de dados fornecidas e armazenando-os em uma visualização analítica por meio de SQL CTE's

Assim que adiciono mais de dois CTEs ao meu modelo de dados, a saída final muda da saída original correta.

Por exemplo, quando corro no começo:

WITH USER_INFO AS 
(
    SELECT
        ID AS ACCOUNT_ID,
        STATUS,
        CREATED_AT AS REGISTRATION_DATE
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.ACCOUNT
    WHERE
        STATUS IS NULL
),
WEIGHT_LOG AS 
(
    SELECT
        wl.ID AS WEIGHT_LOG_ID,
        wl.ACCOUNT_ID,
        wl.WEIGHT,
        wl.CREATED_AT
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.WEIGHT_LOG wl
    LEFT JOIN
        USER_INFO ui ON wl.ACCOUNT_ID = ui.ACCOUNT_ID
    WHERE
        wl.CREATED_AT > ui.REGISTRATION_DATE
)
SELECT 
    ui.ACCOUNT_ID,
    COUNT(wl.WEIGHT_LOG_ID) AS WEIGHT_LOG_COUNT,
FROM
    USER_INFO ui
LEFT JOIN
    WEIGHT_LOG wl ON ui.ACCOUNT_ID = wl.ACCOUNT_ID
GROUP BY
    ui.ACCOUNT_ID
ORDER BY
    ui.ACCOUNT_ID;

Eu obtenho a contagem correta de linhas para cada usuário na weight_logtabela em nosso banco de dados menos a entrada que está sendo gerada durante o processo de integração (data de registro).

Visualização da tabela Pre_Join

Account_ID 879 = 4 
Account_ID 881 = 1

Agora, quando tento adicionar outra coluna à minha instrução select final Reflection_Countque representa a contagem total de reflexões distintas que um usuário concluiu, meus dados para meu log de peso mudam e estão errados.

WITH USER_INFO AS 
(
    SELECT
        ID AS ACCOUNT_ID,
        STATUS,
        CREATED_AT AS REGISTRATION_DATE
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.ACCOUNT
    WHERE
        STATUS IS NULL
),
WEIGHT_LOG AS 
(
    SELECT
        wl.ID AS WEIGHT_LOG_ID,
        wl.ACCOUNT_ID,
        wl.WEIGHT,
        wl.CREATED_AT
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.WEIGHT_LOG wl
    LEFT JOIN
        USER_INFO ui ON wl.ACCOUNT_ID = ui.ACCOUNT_ID
    WHERE
        wl.CREATED_AT > ui.REGISTRATION_DATE
),
REFLECTIONS AS 
(
    WITH REFLECTION_PROGRESS_LOG AS 
    (
        SELECT
            ACCOUNT_ID,
            ID AS REFLECTION_PROGRESS_ID,
            EXTERNAL_REFLECTION_ID,
            CREATED_AT
        FROM
            LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.REFLECTION_PROGRESS
        ORDER BY
            ACCOUNT_ID
    ),
    REFLECTION_DETAILS AS 
    (
        SELECT 
            ID AS REFLECTION_ID,
            EXTERNAL_ID,
            TITLE AS REFLECTION_TITLE,
            LANGUAGE
        FROM 
            LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.REFLECTION
        WHERE
            LANGUAGE = 'en'
    )
    SELECT
        rpl.ACCOUNT_ID,
        rpl.REFLECTION_PROGRESS_ID,
        rpl.EXTERNAL_REFLECTION_ID,
        r.REFLECTION_TITLE,
        rpl.CREATED_AT
    FROM
        REFLECTION_PROGRESS_LOG rpl
    LEFT JOIN
        REFLECTION_DETAILS r ON rpl.EXTERNAL_REFLECTION_ID = r.EXTERNAL_ID
    ORDER BY 
        ACCOUNT_ID,
        REFLECTION_PROGRESS_ID
)
SELECT 
    ui.ACCOUNT_ID,
    COUNT(wl.WEIGHT_LOG_ID) AS WEIGHT_LOG_COUNT,
    COUNT(DISTINCT r.EXTERNAL_REFLECTION_ID) AS REFLECTION_COUNT
FROM
    USER_INFO ui
LEFT JOIN
    WEIGHT_LOG wl ON ui.ACCOUNT_ID = wl.ACCOUNT_ID
LEFT JOIN
    REFLECTIONS r ON ui.ACCOUNT_ID = r.ACCOUNT_ID
GROUP BY
    ui.ACCOUNT_ID
ORDER BY
    ui.ACCOUNT_ID;

Visualização da tabela Post_Join

Account_ID 879 = 12 
Account_ID 881 = 1

Não consigo localizar o problema e agradeceria qualquer informação.

Obrigado

sql