Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT o.orders_id AS orders_id -- '№ заказа'
- , o.customers_id AS customers_id -- 'ID клиента'
- , IFNULL(umS.utm_value, '') AS UTM_source -- 'UTM_source (заказ)'
- , IFNULL(umM.utm_value, '') AS UTM_medium -- 'UTM_medium (заказ)'
- , IFNULL(umС.utm_value, '') AS UTM_campaign -- 'UTM_campaign (заказ)'
- , IFNULL(umT.utm_value, '') AS UTM_term -- 'UTM_term (заказ)'
- , IFNULL(umContent.utm_value, '') AS UTM_content -- 'UTM_content (заказ)'
- , os.orders_status_id
- , o.customers_status AS current_customers_status_id
- , IF(os.orders_status_id IN (10, 22), 'Доставлен', os.orders_status_name) AS orders_status_name -- 'Статус заказа'
- , IF(o.is_partial_return = 1, 'Частичный возврат', os.orders_status_name) AS orders_status_partial_return_name -- 'Статус заказа (с возвратом)'
- , IF(ot.discount_type = 2, ot.discount, 0) AS orders_sum_discount_promo -- 'Скидка за промо-код, руб.'
- , CAST(IFNULL(ot.discount, 0) AS DECIMAL(15,2)) AS orders_sum_discount
- , IFNULL(ot.discount_type, 0) AS discount_type
- , IFNULL(ot.credit,0) AS orders_sum_credit -- 'Бонусы в заказе'
- , IFNULL((SELECT
- pl.number
- FROM points_log_to_orders AS plto
- LEFT JOIN remote_points_log AS pl
- ON pl.points_log_id = plto.points_log_id
- AND pl.points_change_type_id IN (13, 14, 15, 16, 17, 18)
- WHERE plto.orders_id = o.orders_id
- ORDER BY plto.points_log_id DESC
- LIMIT 1
- )
- , 0) AS orders_sum_points_count -- 'Баллы в заказе'
- , CAST(IFNULL(ot.total, 0) + IFNULL(ot.noncash, 0) AS DECIMAL(15,2)) AS orders_sum_total -- 'Сумма заказа, руб.'
- , IFNULL(ot.noncash, 0) AS orders_sum_noncash
- , IFNULL(ot.subtotal, 0) AS orders_sum_subtotal
- , IFNULL(ot.shipping_original,0) AS orders_sum_shipping_original
- , o.orders_consolidation
- , o.shipping_class AS shipping_class -- 'Способ доставки'
- , CAST(IFNULL(ot.shipping,0) AS DECIMAL(15,2)) AS orders_sum_shipping -- 'Стоимость доставки, руб.'
- , IFNULL(ot.service_cost,0) AS orders_sum_service_cost -- 'Стоимость сервисного сбора, руб.'
- , CONCAT(o.payment_method) AS payment_method -- 'Способ оплаты'
- , IFNULL(CONCAT(bg.boxes_group_title, ' (', b.boxes_title, ')'), 'без упаковки') AS boxes_type -- 'Упаковка'
- , o.customers_fias_id
- , IF(o.customers_fias_id IS NULL OR o.customers_fias_id = '' OR f.id IS NULL, TRIM(COALESCE(o.customers_city, '')), COALESCE(f.offname, '')) AS orders_delivery_sity -- 'Название нас.пункта доставки'
- , IFNULL(op.platform, '') AS orders_platform_source -- 'Устройство, с которого сделан заказ'
- , DATE(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00')) AS orders_purchased_date
- , CAST(DATE_FORMAT(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00'),'%Y%m%d') AS INT) AS orders_purchased_date_id
- , TIME(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00')) AS orders_purchased_time -- 'Время заказа'
- , CAST(DATE_FORMAT(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00'),'%H%i%s') AS INT) AS orders_purchased_time_id
- , DATE(COALESCE(o.date_delivery_real, '0000-00-00')) AS orders_date_delivery_real
- , CAST(DATE_FORMAT(COALESCE(o.date_delivery_real, '0000-00-00'),'%Y%m%d') AS INT) AS orders_date_delivery_real_id
- , IFNULL(pr.promocodes_word, '') AS promocodes_name -- 'Название промокода'
- , IFNULL(pr.promocodes_value, '') AS promocodes_value -- 'Сумма скидки за промокод'
- , IF((SELECT MIN(oo.orders_id) FROM orders AS oo WHERE oo.customers_id = o.customers_id) = o.orders_id, 1,0) AS is_first_customers_orders
- , IFNULL(osr.parent_orders_id, o.orders_id) AS parent_orders_id
- , IF(osr.parent_orders_id IS NULL, COALESCE((SELECT distinct 1 FROM orders_separate_results AS osr2 WHERE osr2.parent_orders_id = o.orders_id),0), 2) parent_orders_type_id
- , o.last_date_update AS last_date_update
- , c.sites_id AS sites_id
- , o.customers_postamat_id AS postamat_id
- FROM orders AS o
- JOIN orders_status AS os ON os.orders_status_id = o.orders_status
- LEFT JOIN fias AS f ON f.id = o.customers_fias_id
- JOIN customers c ON c.customers_id = o.customers_id
- LEFT JOIN customers_source cs ON cs.customers_id = c.customers_id
- LEFT JOIN utm_marks_binds umb ON umb.utm_marks_binds_id = cs.utm_marks_binds_id
- LEFT JOIN utm_marks umS ON umS.utm_marks_id = umb.utm_source
- LEFT JOIN utm_marks umM ON umM.utm_marks_id = umb.utm_medium
- LEFT JOIN utm_marks umС ON umС.utm_marks_id = umb.utm_campaign
- LEFT JOIN utm_marks umT ON umT.utm_marks_id = umb.utm_term
- LEFT JOIN utm_marks umContent ON umContent.utm_marks_id = umb.utm_content
- LEFT JOIN orders_total ot ON ot.orders_id = o.orders_id
- LEFT JOIN boxes b ON b.boxes_id = o.orders_box_id
- LEFT JOIN boxes_group bg ON bg.boxes_group_id = b.boxes_group_id
- LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
- -- LEFT JOIN tmp_orders_first_customer_order as tmporfco on tmporfco.orders_id = o.orders_id
- LEFT JOIN orders_to_promocodes AS otp ON otp.orders_id = o.orders_id
- LEFT JOIN promocodes AS pr ON pr.promocodes_id = otp.promocodes_id
- LEFT JOIN orders_separate_results AS osr ON osr.orders_id = o.orders_id
- LEFT JOIN orders AS po ON osr.parent_orders_id = po.orders_id
- JOIN dwh_upload_config AS duc on duc.component = 'dwh_orders'
- WHERE
- o.orders_id = 2484143
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement