Skip to main content
deleted 53 characters in body
Source Link

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

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 ratio below. For clarity, I also included output columns Unq_Cnt that counts unique names to date (i.e. a restricted running count) and Tot_Cnt counts all Name values to date (i.e. a simple running count). The output Ratio_of_Cnts field is a just a running proportion of the two.

Dt          Name    Unq_Cnt Tot_Cnt     Ratio_of_Cnts
2012-01-01  a       1           1       1.0000
2012-01-02  b       2           2       1.0000
2012-01-03  c       3           3       1.0000
2012-01-04  a       3           4       0.7500
2012-01-05  b       3           5       0.6000
2012-01-06  d       4           6       0.6666
2012-01-07  a       4           7       0.5714
2012-01-08  b       4           8       0.5000
2012-01-09  e       4           9       0.4444
2012-01-10  e       4           10      0.4000

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 Ratio below. For clarity, I also included output columns UCnt that counts unique names to date (i.e. a restricted running count) and TCnt counts all names to date (i.e. a simple running count). The output Ratio field is a just a running proportion of the two.

Dt          Name    UCnt    TCnt    Ratio
2012-01-01  a       1       1       1.000
2012-01-02  b       2       2       1.000
2012-01-03  c       3       3       1.000
2012-01-04  a       3       4       0.750
2012-01-05  b       3       5       0.600
2012-01-06  d       4       6       0.666
2012-01-07  a       4       7       0.571
2012-01-08  b       4       8       0.500
2012-01-09  e       5       9       0.555
2012-01-10  e       5       10      0.500
Source Link

Running ratio of unique counts to total counts

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 ratio below. For clarity, I also included output columns Unq_Cnt that counts unique names to date (i.e. a restricted running count) and Tot_Cnt counts all Name values to date (i.e. a simple running count). The output Ratio_of_Cnts field is a just a running proportion of the two.

Dt          Name    Unq_Cnt Tot_Cnt     Ratio_of_Cnts
2012-01-01  a       1           1       1.0000
2012-01-02  b       2           2       1.0000
2012-01-03  c       3           3       1.0000
2012-01-04  a       3           4       0.7500
2012-01-05  b       3           5       0.6000
2012-01-06  d       4           6       0.6666
2012-01-07  a       4           7       0.5714
2012-01-08  b       4           8       0.5000
2012-01-09  e       4           9       0.4444
2012-01-10  e       4           10      0.4000