Início / dba / Perguntas / 331465
Accepted
Bart Jonk
Asked: 2023-09-22 16:50:34 +0800 CST

Como usar o WITH da melhor maneira para parametrizar uma data

  • 772

Adoro usar CTEs para criar consultas claras e agradáveis. No entanto, tenho certeza de que a consulta que criei é realmente ineficiente.

Existe uma maneira melhor de fazer isso e manter as coisas claras?

with first_date as (
    -- selecting just 1 date 
    --    namely:  1 full year ago
    select (extract(year from current_date - interval '1 year')||'-01-01' )::date as date
    )
, last_date as (
    select date from star.dim_date where current_cal_day='Current'
)
, total_active_customers_ps_day as(
  select
        dd.date
        , dd.is_first_day_in_month
        , dd.is_last_day_in_month   
        , count(dc.id) as total_customers
    from first_date, last_date,
        star.dim_date dd
        -- join with dim_client, using first_subscription_start_date & last_subscription_end_date
        --   to get the ids of just the active clients 
        join star.dim_client dc on dd.date 
            between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01') 
            and dc.created <= dd.date
            and dc.first_subscription_start_date >= dc.created::date
    where 
        dd.date >= first_date.date
        and dd.date <= last_date.date
    group by 
        dd.date
        , dd.is_first_day_in_month 
        , dd.is_last_day_in_month   
)
select * from total_active_customers_ps_day ;

Acho que estou causando algumas junções cartesianas, já que essa consulta é bem mais eficiente

with total_active_customers_ps_day as(
  select
        dd.date
        , dd.is_first_day_in_month
        , dd.is_last_day_in_month   
        , count(dc.id) as total_customers
    from 
        star.dim_date dd
        -- join with dim_client, using first_subscription_start_date & last_subscription_end_date
        --   to get the ids of just the active clients 
        join star.dim_client dc on dd.date 
            between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01') 
            and dc.created <= dd.date
            and dc.first_subscription_start_date >= dc.created::date
    where 
        dd.date >=  (extract(year from current_date - interval '1 year')||'-01-01' )::date
        and dd.date <= (select date from star.dim_date where current_cal_day='Current')
    group by 
        dd.date
        , dd.is_first_day_in_month 
        , dd.is_last_day_in_month   
)
select * from total_active_customers_ps_day ;

Qual é a melhor maneira de fazer isso?

postgresql

1 respostas

  1. Best Answer

    Em vez de usar uma subconsulta na WHEREcláusula para buscar a data de término, você pode calculá-la uma vez em uma expressão de tabela comum (CTE) no início da consulta, semelhante ao que fez para a data de início. Isto reduz a complexidade da WHEREcláusula e potencialmente torna a consulta mais eficiente.

    De fato, existe uma junção cartesiana entre a star.dim_datetabela e os CTEs start_datee end_date. Isto poderia levar a problemas de desempenho, especialmente se a star.dim_datetabela contiver um grande número de linhas.

    Para evitar a junção cartesiana e tornar a consulta mais eficiente, você pode usar um único CTE para calcular as datas de início e término e depois unir esse CTE à star.dim_datetabela.

    Também usei a DATE_TRUNCfunção para calcular a data de início há um ano a partir da data atual, o que pode tornar a consulta mais intuitiva.

    WITH date_range AS (
    SELECT
        (DATE_TRUNC('YEAR', CURRENT_DATE) - INTERVAL '1 YEAR')::DATE AS start_date,
        DATE_TRUNC('DAY', CURRENT_DATE) AS end_date
),

total_active_customers_per_day AS (
    SELECT
        dd.date,
        dd.is_first_day_in_month,
        dd.is_last_day_in_month,
        COUNT(dc.id) AS total_customers
    FROM
        star.dim_date dd
    JOIN
        star.dim_client dc ON dd.date BETWEEN dc.first_subscription_start_date AND COALESCE(dc.last_subscription_end_date::DATE, '3000-01-01')
            AND dc.created <= dd.date
            AND dc.first_subscription_start_date >= dc.created::DATE
    JOIN
        date_range ON dd.date BETWEEN date_range.start_date AND date_range.end_date
    GROUP BY
        dd.date,
        dd.is_first_day_in_month,
        dd.is_last_day_in_month
)

SELECT * FROM total_active_customers_per_day;

    Certifique-se de que as colunas usadas nas JOINcondições e na WHEREcláusula estejam indexadas corretamente. No seu caso, indexar colunas como dd.date, dc.first_subscription_start_datee dc.last_subscription_end_datepode melhorar significativamente o desempenho da consulta.

    • 2

relate perguntas