How to calculate aggregate function COUNT(DISTINCT ) over values previous to one value?

user_11727173 0
Viewed 89

I have employee records on Google BigQuery containing : employee_identifier, manager_identifier and date_of_the_record

My goal is to calculate, through an SQL query, for each record, the number of managers an employee had until the date of the record.

I have tried different clauses : OVER (PARTITION BY / ROWS UNBOUNDED PRECEDING), etc.

What I have tried is :

SELECT 
  employee_identifier, 
  date_of_the_record,
  COUNT(DISTINCT manager_identifier) 
    OVER (PARTITION BY employee_identifier ORDER BY date_of_the_record ROWS UNBOUNDED PRECEDING) AS number_of_managers_until_date_of_the_record
FROM employee_database

but the DISTINCT clause is forbidden with ORDER BY.

To sum it up, I just want the number of (distinct) managers an employee had until the date of the record.

sql
2 Answers

You could achieve this using a correlated sub-query, The following query should do what you want

CREATE TABLE #emp (employee_identifier INT,date_of_the_record DATE,manager_identifier INT)

INSERT INTO #emp VALUES
(1,getdate()-90,10),
(1,getdate()-80,20),
(1,getdate()-70,30),
(1,getdate()-60,10),
(1,getdate()-30,40),
(1,getdate()-20,80)

SELECT 
employee_identifier, 
date_of_the_record,
(SELECT COUNT(DISTINCT (manager_identifier)) FROM #emp e WHERE e.employee_identifier = emp.employee_identifier AND e.date_of_the_record <= emp.date_of_the_record) AS number_of_managers_until_date_of_the_record
FROM #emp emp
GROUP BY employee_identifier, 
date_of_the_record

The result is as below,

employee_identifier date_of_the_record  number_of_managers_until_date_of_the_record
1                   2019-04-03          1
1                   2019-04-13          2
1                   2019-04-23          3
1                   2019-05-03          3
1                   2019-06-02          4
1                   2019-06-12          5
user_81551440

Below is for BigQuery Standard SQL 

#standardSQL
SELECT * EXCEPT(arr),
  (SELECT COUNT(DISTINCT id) FROM UNNEST(arr) id) AS number_of_managers_until_date_of_the_record
FROM (
  SELECT *, ARRAY_AGG(manager_identifier) OVER(win) arr
  FROM `project.dataset.employee_database`
  WINDOW win AS (PARTITION BY employee_identifier ORDER BY date_of_the_record)
)
user_52219440
Related