CODEWITHSUNDEEP
sqlgoogle-bigquery
konstantpe
konstantpe

Reputation: 11

Calculating on different rows within one column in SQL

I have a table with the following information, I'm using Google BigQuery. I'm trying to aggregate by person_ID according to a few different types of calculations, the number of days between middle and initial, end and initial, and close and initial.

|Person_ID|Action |Date       |
|100      |Initial|22/12/2018 |
|100      |Middle |23/12/2018 |
|100      |End    |29/12/2018 |
|100      |Close  |31/12/2018 |
|150      |Initial|02/01/2019 |
|150      |Middle |04/01/2019 |
|150      |End    |07/01/2019 |
|150      |Close  |10/01/2019 |

I'm trying to end up with the result as follows

|Person_ID|Middle_Minus_initial|End_Minus_initial|Close_Minus_initial|
|100      |         1          |         7       |          9        |
|150      |         2          |         5       |          8        |

I'm not really sure how to go about it at all as I'm quite the beginner when it comes to SQL so any help would be appreciated. Thanks.

Upvotes: 1

Views: 75

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
SELECT Person_ID,
  DATE_DIFF(Middle, Initial, DAY) AS Middle_Minus_initial,
  DATE_DIFF(`End`, Initial, DAY) AS End_Minus_initial,
  DATE_DIFF(Close, Initial, DAY) AS Close_Minus_initial
FROM (
  SELECT Person_ID, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Initial', `Date`, NULL))) AS Initial, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Middle', `Date`, NULL))) AS Middle, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'End', `Date`, NULL))) AS `End`, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Close', `Date`, NULL))) AS Close
  FROM `project.dataset.table`
  GROUP BY Person_ID
)

You can test, play with above using sample data from your question as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 100 Person_ID, 'Initial' Action, '22/12/2018' `Date` UNION ALL 
  SELECT 100, 'Middle', '23/12/2018' UNION ALL 
  SELECT 100, 'End', '29/12/2018' UNION ALL 
  SELECT 100, 'Close', '31/12/2018' UNION ALL 
  SELECT 150, 'Initial', '02/01/2019' UNION ALL 
  SELECT 150, 'Middle', '04/01/2019' UNION ALL 
  SELECT 150, 'End', '07/01/2019' UNION ALL 
  SELECT 150, 'Close', '10/01/2019' 
)
SELECT Person_ID,
  DATE_DIFF(Middle, Initial, DAY) AS Middle_Minus_initial,
  DATE_DIFF(`End`, Initial, DAY) AS End_Minus_initial,
  DATE_DIFF(Close, Initial, DAY) AS Close_Minus_initial
FROM (
  SELECT Person_ID, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Initial', `Date`, NULL))) AS Initial, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Middle', `Date`, NULL))) AS Middle, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'End', `Date`, NULL))) AS `End`, 
    PARSE_DATE('%d/%m/%Y', MAX(IF(Action = 'Close', `Date`, NULL))) AS Close
  FROM `project.dataset.table`
  GROUP BY Person_ID
)
-- ORDER BY Person_ID

with result 

Row Person_ID   Middle_Minus_initial    End_Minus_initial   Close_Minus_initial  
1   100         1                       7                   9    
2   150         2                       5                   8

Upvotes: 1

GMB
GMB

Reputation: 222462

Another option, which avoids using aggregation, is to JOIN several subqueries, like :

SELECT
    t.personid, 
    DATEDIFF(tm.date, ti.date, day) Middle_Minus_initial,
    DATEDIFF(te.date, ti.date, day) End_Minus_initial,
    DATEDIFF(tc.date, ti.date, day) Close_Minus_initial 
FROM 
    (SELECT DISTINCT personid FROM mytable) t
    LEFT JOIN mytable ti ON ti.personid = t.personid  AND ti.action = 'Initial'
    LEFT JOIN mytable tm ON tm.personid = t.personid AND tm.action = 'Middle'
    LEFT JOIN mytable te ON te.personid = t.personid AND te.action = 'End'  
    LEFT JOIN mytable tc ON tc.personid = t.personid AND tc.action = 'Close'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One method is conditional aggregation:

select person_id,
       date_diff(max(case when action = 'Middle' then date end),
                 max(case when action = 'Initial' then date end),
                 day) as middle_minus_initial,
       date_diff(max(case when action = 'End' then date end),
                 max(case when action = 'Initial' then date end),
                 day) as end_minus_initial,
       date_diff(max(case when action = 'Close' then date end),
                 max(case when action = 'Initial' then date end),
                 day) as close_minus_initial
from t
group by person_id;

Upvotes: 0

Related Questions