Partition by with condition statement

user_7920004 0
Viewed 11659

I have data of products that are sold by various shops. For some shops they are sold with discount mapped by PROMO_FLG. I would like to display two COUNT PARTITION columns.

+-------------------------+--------------+---------------------+
| Store                   | Item         | PROMO_FLG|
|-------------------------+--------------+---------------------|
| 1                       |            1 |                   0 |
| 2                       |            1 |                   1 |
| 3                       |            1 |                   0 |
| 4                       |            1 |                   0 |
| 5                       |            1 |                   1 |
| 6                       |            1 |                   1 |
| 7                       |            1 |                   1 |
| 8                       |            1 |                   0 |
| 9                       |            1 |                   0 |
| 10                      |            1 |                   0 |
+-------------------------+--------------+---------------------+

First displays all shops that thave this product (which is done)

COUNT(DISTINCT STORE) OVER (PARTITION ITEM) would give is 10

Second one - which I seek - counts only these shops that have value in PROMO_FLG = 1 attribute.

That should give us value of 4

snowflake-cloud-data-platform analysis sql database
2 Answers

I think you want:

select t.*,
       count(*) over (partition by item) as num_stores,
       sum(promo_flg) over (partition by item) as num_promo_1
from t;

If you actually need distinct counts:

select t.*,
       count(distinct store) over (partition by item) as num_stores,
       count(distinct case when promo_flg = 1 then store end) over (partition by item) as num_promo_1
from t;

Here is a db<>fiddle. The fiddle uses Oracle because it supports COUNT(DISTINCT) as a window function.

Here is an alternative, if the window functions don't work:

select *
from t join
     (select item, count(distinct store) as num_stores, count(distinct case when promo_flg = 1 then store end) as num_stores_promo
      from t
      group by item
     ) tt
     using (item);
user_11440350

using Gordon second SQL but showing it working in Snowflake

select v.*
    ,count(distinct store) over (partition by item) as num_stores
    ,count(distinct iff(promo_flg = 1, store, null)) over (partition by item) as num_dis_promo_stores
    ,sum(iff(promo_flg = 1, 1, 0)) over (partition by item) as num_sum_promo_stores
from values
  (1 , 1, 0 ),
  (2 , 1, 1 ),
  (3 , 1, 0 ),
  (4 , 1, 0 ),
  (5 , 1, 1 ),
  (6 , 1, 1 ),
  (7 , 1, 1 ),
  (8 , 1, 0 ),
  (9 , 1, 0 ),
  (10, 1, 0 )
  v(store, item, promo_flg) ;

gives:

STORE   ITEM    PROMO_FLG   NUM_STORES  NUM_DIS_PROMO_STORES    NUM_SUM_PROMO_STORES
1       1       0           10          4                       4
2       1       1           10          4                       4
3       1       0           10          4                       4
4       1       0           10          4                       4
5       1       1           10          4                       4
6       1       1           10          4                       4
7       1       1           10          4                       4
8       1       0           10          4                       4
9       1       0           10          4                       4
10      1       0           10          4                       4

So depending if you want the distinct count, or the sum, both, I have used the non standard SQL form that snowflake support iff as I prefer it for being smaller sql. But you can see them working.

testing Gordons second case count(distinct case when promo_flg = 1 then store end) over (partition by item) as num_promo_1 worked as written.

To respond to Marcin2x4 question on Gordons answer, you get different results form the methods if/when the data deviates from how you have described it. Thus in the case you have a store has an item and multiple rows with promo_flg present. Or if the promo_flg has non-zero values:

select v.*
    ,count(distinct store) over (partition by item) as num_stores
    ,count(distinct iff(promo_flg = 1, store, null)) over (partition by item) as num_dis_promo_stores
    ,sum(iff(promo_flg <> 0, 1, 0)) over (partition by item) as num_sum_promo_stores
    ,sum(promo_flg) over (partition by item) as num_promo_1
    ,count(distinct case when promo_flg = 1 then store end) over (partition by item) as num_promo_1
from values
  (1 , 1, 0 ),
  (2 , 1, 1 ),
  (3 , 1, 0 ),
  (4 , 1, 0 ),
  (5 , 1, 1 ),
  (6 , 1, 1 ),
  (7 , 1, 1 ),
  (8 , 1, 0 ),
  (9 , 1, 0 ),
  (10, 1, 0 ),
  (7, 1, 1 ),
  (7, 1, 2 )
  v(store, item, promo_flg) ;

then num_dis_promo_stores & num_promo_1 give 4, num_sum_promo_stores gives 6, & num_promo_1 gives 7

user_439920
Related