Skip to content

Instantly share code, notes, and snippets.

@snakers4
Created March 6, 2017 10:08
Show Gist options
  • Save snakers4/b60f7cdf4fd1bbb789d8fc5b5496b2c0 to your computer and use it in GitHub Desktop.
Save snakers4/b60f7cdf4fd1bbb789d8fc5b5496b2c0 to your computer and use it in GitHub Desktop.
Ultimate example
SELECT
a.order_id as order_id,
a.creation_time,
a.finalize_time,
a.c_user_id,
a.ug_id,
a.sales_type,
a.payment_type,
a.delivery_date,
a.ag_type_name,
a.STORE_ID,
SUM(a.margin) as margin,
SUM(a.revenue) as revenue,
LISTAGG(a.arg_id , ', ') WITHIN GROUP (ORDER BY a.order_id) as article_group_list
FROM
(
SELECT DISTINCT
O.ID as order_id,
O.CREATION_DATE as creation_time,
O.TRANSACTION_DATE as finalize_time,
--O_ITEMS.ID as oi_id,
-- SL.AMOUNT as sl_amount,
U.ID as c_user_id,
UG.ID as ug_id,
CASE O.SALE_TYPE_ID
WHEN 3 THEN 'Интернет магазин'
WHEN 1 THEN 'Опт'
WHEN 2 THEN 'Розница'
WHEN 4 THEN 'Терминал'
WHEN 5 THEN 'Офис'
END as sales_type,
CASE O.SALE_TYPE_ID
WHEN 4 THEN 'кредит'
WHEN 5 THEN 'взаиморасчёты'
WHEN 1 THEN 'безналичная'
WHEN 2 THEN 'наличная'
WHEN 3 THEN 'эквайринг'
END as payment_type,
O.DELIVERY_DATE as delivery_date,
AGT.NAME as ag_type_name,
O.STORE_ID,
-SUM(SL.AMOUNT) as margin,
-SUM(CASE WHEN SL.AMOUNT > 0 THEN 0 ELSE SL.AMOUNT END) as revenue,
ARG.ID as arg_id
FROM
/*
Trying to analyze sales, i.e. the most simple anomaly detection using the following features
*/
ULTIMA.D_SALES o
JOIN ULTIMA.TP_SALE_ARTICLES o_items ON O.ID = O_ITEMS.ORIGINAL_SALE_DOCUMENT_ID
JOIN ULTIMA.TD_SALES sl ON o.ID = sl.DOCUMENT_ID
JOIN KERNEL.USERS u ON O.CREATOR_ID = U.ID
JOIN KERNEL.USER_GROUPS ug ON UG.ID = U.GROUP_ID
JOIN ULTIMA.AGENTS ag ON AG.ID = O.AGENT_ID
JOIN ULTIMA.AGENT_TYPES agt ON AGT.ID = AG.TYPE_ID
JOIN ULTIMA.ARTICLES ar ON AR.ID = sl.ARTICLE_ID
JOIN ULTIMA.ARTICLE_GROUPS arg ON ARG.ID = AR.GROUP_ID
WHERE 1=1
--AND o.id = 252261
GROUP BY
O.ID,
O.CREATION_DATE,
O.TRANSACTION_DATE,
--O_ITEMS.ID,
-- SL.AMOUNT,
U.ID,
UG.ID,
--U.NAME,
--UG.NAME,
CASE O.SALE_TYPE_ID
WHEN 3 THEN 'Интернет магазин'
WHEN 1 THEN 'Опт'
WHEN 2 THEN 'Розница'
WHEN 4 THEN 'Терминал'
WHEN 5 THEN 'Офис'
END,
CASE O.SALE_TYPE_ID
WHEN 4 THEN 'кредит'
WHEN 5 THEN 'взаиморасчёты'
WHEN 1 THEN 'безналичная'
WHEN 2 THEN 'наличная'
WHEN 3 THEN 'эквайринг'
END,
O.DELIVERY_DATE,
AGT.NAME,
O.STORE_ID,
arg.id
FETCH FIRST 50000 ROWS ONLY
) a
GROUP BY
a.order_id,
a.creation_time,
a.finalize_time,
a.c_user_id,
a.ug_id,
a.sales_type,
a.payment_type,
a.delivery_date,
a.ag_type_name,
a.STORE_ID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment