I have a dataset as below:

date        store    employee     products     sales        
20210101       a        ben          5         laptop        
20210101       a        ben         10         monitor       
20210201       b        tim         15         laptop        
20210301       b        tim         10         tablet         
20210301       a        ann         30         monitor        

What I would like to do is to calculate the ratio of how many products the employee sold per minute of their working shift. Every working day every employee has 6 hours shift. For example: ben has a ratio of (5 10)/(6*60) = 0.04 on Jan 01, 2021.

I want to create a dynamic calculation so if we choose store a, it has a ratio of sold products for all the employees and their shifts. For example: store a has a ratio in total: (5 10 30) / (6*60*2) = 0.06

If we choose laptop, it should have a ratio of: (5 10 30) / (6*60*2) = 0.06

Here are the query that I have tried:

(SUM('products') OVER (PARTITION BY 'date', 'store', 'employee', 'sales')) / (6*60) 

However, this calculation is not dynamic and I think I'm missing something. If anyone can give me a suggestion, I would really appreciate.

CodePudding user response：

A simple group by with rollup should do the trick here. You will be able to see the sales_per_minute at different granularity levels (say store, store employee, or store employee product).

with dummy_data as (
  select '20210101' as date_, 'a' as store, 'ben' as employee, 5 as products, 'laptop' as sales
  union all
  select '20210101', 'a', 'ben', 10, 'monitor'
  union all
  select '20210201', 'a', 'tm', 15, 'laptop'
  union all
  select '20210301', 'a', 'tim', 10, 'tablet'
  union all
  select '20210301', 'a', 'ann', 30, 'monitor'
)
select
  date_,
  store, 
  employee,
  sales,
  ROUND(SUM(products)/(6*60), 2) as sales_per_minute
from
  dummy_data
group by
  date_, store, employee, sales with rollup
order by
  1

Output:

CodePudding user response：

All you want is SUM(products) / 360. Now write the query such that you get the overall ratio or the ratio per employee or the ratio per sales etc. These are merely different groups:

select sum(products) / 360 as per_minute from mytable;

select store, sum(products) / 360 as per_minute from mytable group by store;

select employee, sum(products) / 360 as per_minute from mytable group by employee;

select sales, sum(products) / 360 as per_minute from mytable group by sales;

You can of course also add a WHERE clause to restrict this, like in where date = '20210101'. (Are you really storing dates as strings by the way? Or is this a display thing in your sample data? If it is dates as it should be, use date literals: where date = date '2021-01-01'.)

And of course you can make composite groups, e.g.

select date, employee, sum(products) / 360 as per_minute
from mytable
group by date, employee
order by date, employee;