postgresql - SQL Query to create a field based on Dates

Question

I have a dataset where an id moves through stages A to D sequentially (A to B, B to C & C to D).

I a have timestamps that track when an id entered a particular stage.Here is what my data looks like

So I am interested in creating a field called 'Status' that shows the number of B, C, D converted based on the timestamp of A

For instance;

In the month of Aug'19 The number of 'A's=7

The number of 'B' converted when the timestamp of A is Aug'19 (irrespective of the date under Date_B)=5

The number of 'C' converted when the timestamp of A is Aug'19(irrespective of the date under Date_C)=5

The number of 'D' converted when the timestamp of A is Aug'19(irrespective of the date under Date_D)=5

I tried using union all, but its gives me the 'Status' in real time meaning 'date_B' under id 1 gets a date of Sept'19 not Aug'19.This is the query I used;

SELECT id,
CAST (Date_A AS DATE) AS 'Pivot Date',
'A' AS 'Status'
FROM table1

UNION ALL

SELECT id,
CAST (Date_B AS DATE) AS 'Pivot Date',
'B' AS 'Status'
FROM table1

UNION ALL

SELECT id,
CAST (Date_C AS DATE) AS 'Pivot Date',
'C' AS 'Status'
FROM table1

UNION ALL

SELECT id,
CAST (Date_D AS DATE) AS 'Pivot Date',
'D' AS 'Status'
FROM table1

Can someone point me in the right direction?

Thanks!

标签： postgresqldatecalculated-field

Answer 1

I am not totally sure if this is what you want, but try this:

SELECT
    *, -- or only id or whatever
    COUNT(date_a) OVER (PARTITION BY date_trunc('month', date_a)) AS a_of_the_month,
    COUNT(date_b) OVER (PARTITION BY date_trunc('month', date_a)) AS already_converted_to_b,
    COUNT(date_c) OVER (PARTITION BY date_trunc('month', date_a)) AS already_converted_to_c,
    COUNT(date_d) OVER (PARTITION BY date_trunc('month', date_a)) AS already_converted_to_d
FROM table1;

Perhaps you only wanted the months and your numbers:

SELECT
    date_trunc('month', date_a),
    COUNT(date_a) AS a_of_the_month,
    COUNT(date_b) AS already_converted_to_b,
    COUNT(date_c) AS already_converted_to_c,
    COUNT(date_d) AS already_converted_to_d
FROM table1
GROUP BY date_trunc('month', date_a);

In both cases you might also replace COUNT(date_a) by COUNT(*) if date_a has to be set always.