问题

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 countingthe number of TypeperClientIDandMonth, 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 thenumber of Type per ClientIDandfor each Month : per the last 3 months.

For example :

· For theClientID= XXX, andMonth=8: I want to have the count ofTypewhereMonth=6ANDMonth=7ANDMonth=8

Is there a way to do this withGROUP BY?

Thank you

解答

先按客户id、月份分组统计出类型个数，同时按客户id升序、月份降序排序，再在每个客户id的小组内，过滤出最近三个月的记录即可。这类运算用SQL写起来很麻烦，因为SQL集合化不彻底，只能先算出每个客户id的最大月份，然后关联过滤找出满足条件的记录，最后分组统计个数。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：

SPL源代码：https://github.com/SPLWare/esProc

问答搜集