CODEWITHSUNDEEP
sqlsql-serversql-server-2012
psysky
psysky

Reputation: 3195

data partitioning by criterion in SQL

How can I split the data by the criterion?

SELECT [Dt]
      , [CustomerName]

      , [ItemRelation]

      , [SaleCount]
      , [DocumentNum]
      , [DocumentYear]
      , [IsPromo]
      , [CustomerType]
  FROM [Action]. [Dbo]. [FC]

[IsPromo] has the values ​​0 and 1. I need to divide the data by the number of sales by [SaleCount] for the zero category ispromo!

For example, i have 20 observations by ispromo = 0, where [SaleCount] is only 15 not zero values. Calculate the coefficient, divide the total number of days by the number of days where there were no zero sales by salecount. 15/20 = 0.75. It must be done for each strata (groups) [CustomerName] + [ItemRelation] + [DocumentYear] separately. So, if within group such coefficient is greater than 0.71, then such groups should be written into a table mytab1 if less, then in mytab2

How to do it?

data sample

Dt  CustomerName    ItemRelation    SaleCount   DocumentNum DocumentYear    IsPromo
2018-02-19 00:00:00.000 1   11683   0   999 2018    0
2018-02-20 00:00:00.000 1   11683   0   999 2018    0
2018-02-21 00:00:00.000 1   11683   0   999 2018    0
2018-02-22 00:00:00.000 1   11683   0   999 2018    0
2018-02-23 00:00:00.000 1   11683   0   999 2018    0
2018-02-24 00:00:00.000 1   11683   1339    999 2018    0
2018-02-25 00:00:00.000 1   11683   81  999 2018    0
2018-02-26 00:00:00.000 1   11683   487 999 2018    0
2018-02-27 00:00:00.000 1   11683   861 999 2018    0
2018-02-28 00:00:00.000 1   11683   546 999 2018    0
2018-03-01 00:00:00.000 1   11683   722 999 2018    0
2018-03-02 00:00:00.000 1   11683   890 999 2018    0
2018-03-03 00:00:00.000 1   11683   1128    999 2018    0
2018-03-04 00:00:00.000 1   11683   81  999 2018    0
2018-03-05 00:00:00.000 1   11683   884 999 2018    0
2018-03-06 00:00:00.000 1   11683   3675    999 2018    0
2018-03-07 00:00:00.000 1   11683   3780    999 2018    0
2018-03-08 00:00:00.000 1   11683   3178    999 2018    0
2018-03-09 00:00:00.000 1   11683   1749    999 2018    0
2018-03-10 00:00:00.000 1   11683   1243    999 2018    0

this stratum has coef=0,75 it goes to mytab1

and this stratum

Dt  CustomerName    ItemRelation    SaleCount   DocumentNum DocumentYear    IsPromo
2018-02-19 00:00:00.000 2   11684   0   999 2018    0
2018-02-20 00:00:00.000 2   11684   0   999 2018    0
2018-02-21 00:00:00.000 2   11684   0   999 2018    0
2018-02-22 00:00:00.000 2   11684   0   999 2018    0
2018-02-23 00:00:00.000 2   11684   0   999 2018    0
2018-02-24 00:00:00.000 2   11684   1339    999 2018    0
2018-02-25 00:00:00.000 2   11684   81  999 2018    0
2018-02-26 00:00:00.000 2   11684   487 999 2018    0
2018-02-27 00:00:00.000 2   11684   861 999 2018    0
2018-02-28 00:00:00.000 2   11684   546 999 2018    0
2018-03-01 00:00:00.000 2   11684   722 999 2018    0

has coef 0,545454545 11 days by zero category of ispromo and 6 day was with non zero obs by salescount.

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

A simple method uses avg() with group by:

select CustomerName, ItemRelation, DocumentYear,
       avg( case when IsPromo > 0 then 1.0 end) as promo_ratio
from action.dbo.fc
group by CustomerName, ItemRelation, DocumentYear;

You can then use having avg( case when IsPromo > 0 then 1.0 end) > 0.71 for your filtering.

Upvotes: 2

Related Questions