PostgreSQL showing different time periods in a single query-CodePudding

I have a query that will return the ratio of issuances from (issuances from specific network with specific time period / total issuances). so the issuances from specific network with a specific time period divided to total issuances from all networks. Right now it returns the ratios of issuances only from last year (year-to-date I mean), I want to include several time periods in it such as one month ago, 2 month ago etc. LEFT JOIN usually works but I couldn't figure it out for this one. How do I do it?

Here is the query:

SELECT IR1.network,
count(*) / ((select count(*) FROM issuances_extended  
where status = 'completed' and 
issued_at >= date_trunc('year',current_date)) * 1.) as issuance_ratio_ytd

FROM issuances_extended as IR1 WHERE status = 'completed' and
(issued_at >= date_trunc('year',current_date))

GROUP BY
IR1.network

order by IR1.network

CodePudding user response：

I would break your query into CTEs something like this:

with periods (period_name, period_range) as (
  values 
    ('YTD',   daterange(date_trunc('year', current_date), null)),
    ('LY',    daterange(date_trunc('year', current_date - 'interval 1 year'),
                        date_trunc('year', current_date))),
    ('MTD',   daterange(date_trunc('month', current_date - 'interval 1 month'),
                        date_trunc('month', current_date));
  -- Add whatever other intervals you want to see
), period_totals as ( -- Get period totals
  select p.period_name, p.period_range, count(*) as total_issuances
    from periods p
         join issuances_extended i
           on i.status = 'completed' 
          and i.issued_at <@ p.period_range
)
select p.period_name, p.period_range, 
       i.network, count(*) as network_issuances,
       1.0 * count(*) / p.total_issuances as issuance_ratio
  from period_totals p
       join issuances_extended i
         on i.status = 'completed'
        and i.issued_at <@ p.period_range
 group by p.period_name, p.period_range, i.network, p.total_issuances;

The problem with this is that you get rows instead of columns, but you can use a spreadsheet program or reporting tool to pivot if you need to. This method simplifies the calculations and lets you add whatever period ranges you want by adding more values to the periods CTE.

CodePudding user response：

Something like this? Obviously not tested

 SELECT
    IR1.network,
    count(*)/((select count(*) FROM issuances_extended  
      where status = 'completed' and 
      issued_at between mon.t and current_date ) * 1.) as issuance_ratio_ytd
FROM 
    issuances_extended as IR1 ,
    (
        SELECT
            generate_series('2022-01-01'::date, 
                            '2022-07-01'::date, '1 month') AS t) 
    AS mon
WHERE 
    status = 'completed' and
    (issued_at between mon.t and current_date)
GROUP BY
    IR1.network
ORDER BY
    IR1.network