Any ideas on how to code a running ratio of counts (unique and total) for ordered observations (rows) in t-SQL (SQL Server 2017 Community Edition or higher)?
Given a data set, which is necessarily ordered (Ex. a time series indexed/ordered by date):
CREATE TABLE #x (dt DATE, Name VARCHAR(MAX));
INSERT INTO #x VALUES
('2012-01-01', 'a'),('2012-01-02', 'b'),('2012-01-03', 'c'),
('2012-01-04', 'a'),('2012-01-05', 'b'),('2012-01-06', 'd'),
('2012-01-07', 'a'),('2012-01-08', 'b'),('2012-01-09', 'e'),('2012-01-10', 'e');
So, given columns Dt
and Names
, I need the output ratioRatio
below. For clarity, I also included output columns Unq_CntUCnt
that counts unique names to date (i.e. a restricted running count) and Tot_CntTCnt
counts all Name
valuesnames to date (i.e. a simple running count). The output Ratio_of_CntsRatio
field is a just a running proportion of the two.
Dt Name Unq_CntUCnt Tot_Cnt TCnt Ratio_of_Cnts Ratio
2012-01-01 a 1 1 1.0000000
2012-01-02 b 2 2 1.0000000
2012-01-03 c 3 3 1.0000000
2012-01-04 a 3 4 0.7500750
2012-01-05 b 3 5 0.6000600
2012-01-06 d 4 6 0.6666666
2012-01-07 a 4 7 0.5714571
2012-01-08 b 4 8 0.5000500
2012-01-09 e 4 5 9 0.4444555
2012-01-10 e 4 5 10 0.4000500