pmkhlv

Untitled

May 26th, 2022 (edited)
523
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 3d3XLeq2df
  2.  
  3. CREATE OR REPLACE VIEW analysis.orderitems_v AS (
  4.   SELECT * FROM production.orderitems);
  5.  
  6. CREATE OR REPLACE VIEW analysis.orders_v AS (
  7.   SELECT * FROM production.orders);
  8.  
  9. CREATE OR REPLACE VIEW analysis.orderstatuses_v AS (
  10.   SELECT * FROM production.orderstatuses);
  11.  
  12. CREATE OR REPLACE VIEW analysis.products_v AS (
  13.   SELECT * FROM production.products);
  14.  
  15. CREATE OR REPLACE VIEW analysis.users_v AS (
  16.   SELECT * FROM production.users);
  17.  
  18. CREATE TABLE IF NOT EXISTS analysis.dm_rfm_segments (
  19.     user_id INTEGER,
  20.     recency SMALLINT NOT NULL CHECK (recency BETWEEN 1 AND 5),
  21.     frequency SMALLINT NOT NULL CHECK (frequency BETWEEN 1 AND 5),
  22.     monetary_value SMALLINT NOT NULL CHECK (monetary_value BETWEEN 1 AND 5),
  23.     PRIMARY KEY (user_id),
  24.     FOREIGN KEY (user_id) REFERENCES production.users("id"));
  25.  
  26.  
  27. INSERT INTO analysis.dm_rfm_segments (
  28.     user_id,
  29.     recency,
  30.     frequency,
  31.     monetary_value)
  32.  
  33. SELECT
  34.     id,
  35.     NTILE(5) OVER (ORDER BY last_order_date asc) AS recency_group,
  36.     NTILE(5) OVER (ORDER BY sum_orders asc) AS frequency_group,
  37.     NTILE(5) OVER (ORDER BY sum_payment) AS monetary_group
  38. FROM (
  39.   select
  40.     id, last_order_date, sum_orders, sum_payment
  41.   from
  42.     analysis.users_v u
  43.   left join (select
  44.             distinct(user_id),
  45.             max(order_ts) over (partition by user_id) as last_order_date, -- дата последнего заказа
  46.             count(*) over (partition by user_id) as sum_orders, -- количество заказов у каждого клиента
  47.             sum(payment) over (partition by user_id) as sum_payment -- сумма заказов по клиенту
  48.             from analysis.orders_v
  49.             where order_ts > '2021-01-01' and status = 4) o
  50.   on u.id = o.user_id) users_orders;
  51.  
  52. SELECT count(*) FROM analysis.dm_rfm_segments;
  53.  
  54.  
  55. -- Доработка представлений
  56. CREATE OR REPLACE VIEW analysis.orders_v
  57. AS SELECT orders.order_id,
  58.     orders.order_ts,
  59.     orders.user_id,
  60.     orders.bonus_payment,
  61.     orders.payment,
  62.     orders.cost,
  63.     orders.bonus_grant,
  64.     orders.status
  65. FROM production.orders LEFT JOIN (
  66.      SELECT
  67.         orderstatuslog_v.order_id,
  68.         LAST_VALUE(orderstatuslog_v.status_id) OVER (PARTITION BY orderstatuslog_v.order_id
  69.                                                      ORDER BY orderstatuslog_v.dttm ASC
  70.                                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS status
  71.       FROM
  72.         analysis.orderstatuslog_v) status_query ON production.orders.order_id = status_query.order_id;
RAW Paste Data Copied