SQL Server->> indirectly implements COUNT (DISTINCT XXX) over (PARTITION by YYY ORDER by ZZZ)

The SQL Server 2005 version starts with support for window functions (windowing function) and over words. The SQL Server 2012 version begins to support the sequential/cumulative aggregation of window functions by the ORDER BY clause. However, there is a feature to the SQL Server 2014 version (from the current SQL Server CTP3, or not supported), that is, count (DISTINCT XXX) over (PARTITION by YYY ORDER by ZZZ).

Always feel that this thing can not be done in a more ingenious way, only with cross apply or loop to the data set "UNION all" together. Today on Google found a way others think of, suddenly feel how so stupid, did not think of this method. The trick is to use a subquery with the Dense_rank function with the Max function. Here is the test code:

CREATE TABLEFoo (FookeyINT PRIMARY KEY, CompanyVARCHAR( -), Sales_repVARCHAR( -), ClientVARCHAR( -));INSERT  intoFooVALUES(1,'ABC Corp.','Joe','Client1');INSERT  intoFooVALUES(2,'ABC Corp.','Joe','Client2');INSERT  intoFooVALUES(6,'ABC Corp.','Joe','Client2');INSERT  intoFooVALUES(3,'ABC Corp.','Peter','Client2');INSERT  intoFooVALUES(4,'DEF Corp.','Joe','Client1');INSERT  intoFooVALUES(5,'DEF Corp.','Joe','Client3');SELECT DISTINCTCompany , Sales_rep, client,MAX(RK1) Over(PARTITION bySALES_REP) asrep_distinct_client_cnt,MAX(RK2) Over(PARTITION byCompany ascompany_distinct_client_cnt from (SELECTFookey, Company, Sales_rep, Client,dense_rank () Over(PARTITION bySales_repORDER  byClient asRk1,dense_rank () Over(PARTITION byCompanyORDER  byClient asRK2 fromFoo) asF

The subtle aspect of this approach is the use of the Dense_rank itself to return the same sort number for the same value, which is exactly what we need to distinct. Second, is the same as the count and the number of records is not counted? So is the maximum order number not equal to the value of count? Really smart. Sigh oneself in the past how did not think of it? Still very happy to know this way.

SQL Server->> indirectly implements COUNT (DISTINCT XXX) over (PARTITION by YYY ORDER by ZZZ)