Randomly select between 1-3 values per month

user_4934150 0
Viewed 54

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    customer VARCHAR,
    orderID VARCHAR,
    sales_volume DECIMAL
);

INSERT INTO sales
(event_date, customer, orderID, sales_volume)
VALUES 
('2020-01-08', 'Customer_A', 'Order_001', '130'),
('2020-01-12', 'Customer_A', 'Order_002', '120'),
('2020-01-18', 'Customer_B', 'Order_001', '115'),
('2020-01-22', 'Customer_B', 'Order_002', '300'),
('2020-01-23', 'Customer_B', 'Order_003', '540'),
('2020-01-24', 'Customer_C', 'Order_001', '421'),
('2020-01-26', 'Customer_D', 'Order_001', '198'),

('2020-04-08', 'Customer_B', 'Order_004', '325'),
('2020-04-09', 'Customer_B', 'Order_005', '325'),
('2020-04-11', 'Customer_B', 'Order_006', '425'),
('2020-04-15', 'Customer_D', 'Order_002', '914'),
('2020-04-18', 'Customer_D', 'Order_003', '418'),
('2020-04-20', 'Customer_E', 'Order_001', '723'),
('2020-04-30', 'Customer_C', 'Order_002', '665'),

('2020-06-01', 'Customer_B', 'Order_005', '982'),
('2020-06-15', 'Customer_B', 'Order_006', '100'),
('2020-06-19', 'Customer_C', 'Order_003', '250'),
('2020-06-20', 'Customer_C', 'Order_004', '322'),
('2020-06-30', 'Customer_E', 'Order_002', '924'),
('2020-06-25', 'Customer_A', 'Order_003', '445');

Expected Result:

customer     |   orderid     |      event_date    |     sales_volume
-------------|---------------|--------------------|----------------------
Customer_A   |   Order_002   |      2020-01-12    |       120      
Customer_A   |   Order_001   |      2020-01-08    |       130
Customer_B   |   Order_002   |      2020-01-22    |       300
-------------|---------------|--------------------|------------------------
Customer_E   |   Order_001   |      2020-04-20    |       723
-------------|---------------|--------------------|------------------------
Customer_B   |   Order_005   |      2020-06-01    |       982
Customer_A   |   Order_003   |      2020-06-25    |       445

I have a huge database and need to extract some data from it for a case study.
The problem is that I need to extract the full year of the data because I want to be able to conduct a monthly analysis in the case study. Therefore, I can not limit the extract with dates or LIMIT.

Thus, my idea to solve this issue is a query which extracts randomly 1-3 values per month based on the column customer.

The following conditions should apply:

  1. customer can appear mutliple times in the result --> e.g. Customer_A in the example
  2. the order of the result does not matter --> ORDER BY random()

Do you have any idea if this is possible?
If yes, how do I need to modify the below query?

SELECT
s.customer,
s.orderID,
s.event_date,
SUM(s.sales_volume) AS sales_volume
FROM sales s
GROUP BY 1,2,3
ORDER BY random();
postgresql sql
2 Answers

Does it answer your question ?

db<>fiddle

select
  customer,
  orderid,
  event_date,
  sales_volume
from (
  select
    *,
    row_number() over(partition by customer order by random()) seq
  from (
    SELECT
    s.customer,
    s.orderID,
    s.event_date,
    sum(s.sales_volume) AS sales_volume
    FROM sales s
    GROUP BY 1,2,3
  ) volumes
) sample
where seq <= 3
order by customer, seq
user_121118210

Ammended your DB-Fiddle

Ranked the rows after random per month and fetch 1-3 from the result:

SELECT * from (SELECT
customer,
orderID,
event_date,
sales_volume ,
rank() OVER (PARTITION BY date_trunc('month', event_date) ORDER BY random()) as rand
from sales) temp
where rand < 1 + random() * 3
order by event_date

I ordered by event_date to keep the rows per month together, change it to your needs

And you can raise the chances to get 3 rows by increasing the factor, but limit it to 3, like

where rand < 1 + random() * 6 and rand <= 3
user_60530840
Related