SQL - GROUP BY 3 values of the same column-CodePudding

I have this table in GBQ :

ClientID     Type     Month
  XXX         A         4
  YYY         C         4
  FFX         B         5
  FFF         B         6
  XXX         C         6
  XXX         A         6
  YRE         C         7
  AAR         A         7
  FFF         A         8
  EGT         B         8   
  FFF         B         9
  ETT         C         9

I am counting the number of Type per ClientID and Month, with this basic query :

SELECT    ClientID,
          COUNT(DISTINCT Type) NbTypes,
          Month
FROM      Table
GROUP BY  ClientID, Month

The result looks like this :

ClientID     NbTypes     Month
  XXX           1         4
  XXX           2         6
  FFF           1         6
  FFF           1         8
  FFF           1         9
  ...          ...       ...

What I need to do is, count the number of Type per ClientID and for each Month : per the last 3 months.

For example :

For the ClientID = XXX, and Month = 8 : I want to have the count of Type where Month = 6 AND Month = 7 AND Month = 8

Is there a way to do this with GROUP BY ?

Thank you

CodePudding user response：

You can use a SELECT in a SELECT if that is allowed in Google Big Query

SELECT    ClientID,
          COUNT(DISTINCT Type) NbTypes,
          Month,
          MAX((select count(distinct Type)
                 from Table t2
                where t1.ClientID=t2.ClientID
                  and t1.month-t2.month between 0 and 3               
               )
             )   as NbType_3_months
FROM      Table t1
GROUP BY  ClientID, Month

CodePudding user response：

You could use HAVING in your statement:

SELECT    ClientID,
          COUNT(DISTINCT Type) NbTypes,
          Month
FROM      Table
GROUP BY  ClientID, Month
HAVING Month = EXTRACT(MONTH FROM CURRENT_DATE()) 
       OR Month = EXTRACT(MONTH FROM DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH))
       OR Month = EXTRACT(MONTH FROM DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 2 MONTH))

Note that in your table seems to be no column to determinate the year, so this statement will group all values with month value of the current month to current month minus two months. So for example every data from December, November and October 2021, 2020, 2019 etc. will be selected with this query.

Also note that I could not test this statement, since I don't use BigQuery.
Here is the source for the Date-Functions: https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions