1
votes

Calcul sur différentes lignes dans une colonne en SQL

J'ai un tableau avec les informations suivantes, j'utilise Google BigQuery. J'essaye d'agréger par person_ID selon différents types de calculs, le nombre de jours entre le milieu et l'initiale, la fin et l'initiale, et la fermeture et l'initiale.

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

J'essaye pour finir avec le résultat comme suit

|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 |

Je ne sais pas vraiment comment m'y prendre du tout car je suis assez débutant en matière de SQL, donc tout l'aide serait appréciée. Merci.

sql google-bigquery

3 commentaires

Quelle technologie SQL, et s'il vous plaît ne postez pas de captures d'écran, code postal en ligne.

quel est votre nom dbms?

Excuses, a mis à jour mon message. C'est Google BigQuery

3 Réponses :

0
votes

Une méthode est l'agrégation conditionnelle:

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;

0 commentaires

0
votes

Une autre option, qui évite d'utiliser l'agrégation, consiste à JOINDRE plusieurs sous-requêtes, comme:

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'

0 commentaires

1
votes

Voici pour BigQuery Standard SQL

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

Vous pouvez tester, jouer avec ci-dessus en utilisant des exemples de données de votre question comme dans l'exemple ci-dessous

#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

avec résultat

#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
)

0 commentaires