Question

Source:https://stackoverflow.com/questions/70520454/sql-group-by-3-values-of-the-same-column

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 TypewhereMonth=6ANDMonth=7ANDMonth=8

Is there a way to do this with GROUP BY?

Answer

You can group rows by ClientID and Month, count the number of types and sort rows by ClientID in ascending order and by Month in descending order, and then select from each group the rows of the past three months. It is roundabout and complicated to handle such a scenario in SQL because SQL implements set-orientation only halfway up. For your case, you have to get the largest month for each ClientID, find the eligible records through a join filter, and perform grouping and count. The usual way is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate much simpler code. It gets the task done with only two lines of code:

	A
1	=GBQ.query("SELECT CLIENTID, COUNT(DISTINCT TYPE) AS NBTYPES, MONTH   FROM t2 GROUP BY CLIENTID, MONTH ORDER BY CLIENTID, MONTH DESC")
2	=A1.group@o(#1).run(m=~.#3-3,~=~.select(MONTH>m)).conj()

 

View SPL source code.