导入如下：
set @p_begin_date='2017-06-01';
set @p_end_date='2017-06-30 23:59:59';
set @d_format='%Y-%m-%d';
-- set @area='%深圳%';
SELECT
DATE_FORMAT(a.order_date,@d_f...
/*根据商品码和商品来源两个维度统计*/
set @p_begin='2018-03-20 10:01:20'; /*定义开始时间,统计需要改动这两个时间点*/
set @p_end='2018-03-26 00:00:00'; /*结束时间，不包含这个时间点，这个时间减1秒才是有效时间*/
select
goods_code,
goods_title,
sum(ifnull(discount_fee,0)*ifnull(qty,1)) as 'GMV',
count(order_id) as '订单量',
count(DISTINCT member_id) as '购买此商品会员数',
case when data_source='taobao' then '淘宝'
when data_source='merchants' then '合作商家'
else '自营' END as data_source
from (
SELECT
goods_code,
goods_title,
r1.discount_fee,
r1.order_id ,
r1.member_id,
r1.qty,
replace(substring_index(SUBSTRING_INDEX(m1.remark, ',', 1),':' ,-1),'"','') as 'data_source'
FROM
oddb.fg_order r1 INNER JOIN oddb.fg_order_item m1 on r1.order_id=m1.order_id
WHERE
r1.order_date >= @p_begin and r1.order_date<@p_end and `status` in ('closed','confirmed') )a
group by goods_code order by count(order_id) desc;
/*按地区统计*/
set @p_start='2018-03-28 00:00:00'; -- 开始时间
set @p_end='2018-03-28 23:59:59'; -- 结束时间
SELECT
case when r1.area_id in(
'ebb6f7c4-bb51-11e5-a4b3-00163e0009c6',
'ebb704ef-bb51-11e5-a4b3-00163e0009c6',
'ebb72858-bb51-11e5-a4b3-00163e0009c6',
'ebb726c6-bb51-11e5-a4b3-00163e0009c6',
'ebb7277f-bb51-11e5-a4b3-00163e0009c6',
'ebb7213a-bb51-11e5-a4b3-00163e0009c6',
'ebb72083-bb51-11e5-a4b3-00163e0009c6',
'ebb72376-bb51-11e5-a4b3-00163e0009c6',
'366484f6-886c-11e5-a4b1-fcaa1490ccaf',
'ebb72bff-bb51-11e5-a4b3-00163e0009c6',
'ebb6f131-bb51-11e5-a4b3-00163e0009c6',
'ebb6f5a5-bb51-11e5-a4b3-00163e0009c6'
) then r1.area_name ELSE '其他区' END as 'area_name',
sum(ifnull(a.discount_fee,0)*ifnull(a.qty,1)) as 'GMV',
count(case when a.stores_id is null then ifnull(a.member_id,0)*ifnull(a.qty,1)
else ifnull(a.stores_id,0) end ) as '总订单',
count(DISTINCT a.member_id) as '会员数'
-- sum(case when a.stores_id is null then ifnull(a.total_fee,0)END) as '店东本人购买商品返款价',
-- sum(case when a.stores_id is not null then ifnull(a.total_fee,0) END) as '关联此店东消费者购买商品总返款价',
-- -- sum(case when a.stores_id is null then ifnull(a.discount_fee,0)
-- else ifnull(a.discount_fee,0) end
-- ) as '店铺总到手价',
-- count(case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东本人下单总量',
from
(SELECT
o1.order_id,
o1.order_date,
o1.member_id,
o1.member_type_key,
o1.member_mobile,
a1.stores_id,
o1.discount_fee,
o1.total_fee,
o1.`status`,
o1.order_type,
o1.qty
FROM
oddb.fg_order o1
LEFT JOIN oddb.fg_order_extra a1 ON o1.order_id = a1.order_id
WHERE
order_date BETWEEN @p_start
AND @p_end
AND `status` in ('closed','confirmed'))a
LEFT JOIN mddb.md_stores b ON (case when a.stores_id is null then a.member_id ELSE a.stores_id END) = b.stores_id
left JOIN mddb.md_area r1 on b.area_id=r1.area_code
group by
r1.area_name order by count(a.order_id) desc ;
/*根据店东维度统计数据*/
set @p_start='2018-03-29 00:00:00'; -- 开始日期
set @p_end='2018-03-29 23:59:59'; -- 结束日期
SELECT
case when a.stores_id is null then a.member_mobile ELSE b.contact_tel END as '店东手机',
b.stores_name,
r1.path,
b.address,
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)*ifnull(qty,1) END) as '店东本人购买商品到手价',
sum(case when a.stores_id is not null then ifnull(a.discount_fee,0)*ifnull(qty,1) END) as '关联此店东消费者购买商品总到手价',
ifnull(sum(case when a.stores_id is null then ifnull(a.discount_fee,0)END),0) + ifnull(sum(case when a.stores_id is not null then ifnull(a.discount_fee,0) END),0) as 'GMV',
-- sum(case when a.stores_id is null then ifnull(a.total_fee,0)END) as '店东本人购买商品返款价',
-- sum(case when a.stores_id is not null then ifnull(a.total_fee,0) END) as '关联此店东消费者购买商品总返款价',
-- -- sum(case when a.stores_id is null then ifnull(a.discount_fee,0)
-- else ifnull(a.discount_fee,0) end
-- ) as '店铺总到手价',
-- count(case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东本人下单总量',
count(DISTINCT case when a.stores_id is not null then ifnull(a.member_id,0) END) '会员数',
count(
case when a.stores_id is null then ifnull(a.member_id,0)
else ifnull(a.stores_id,0) end
) as 'order_qty'
from
(SELECT
o1.order_date,
o1.member_id,
o1.member_type_key,
o1.member_mobile,
a1.stores_id,
o1.discount_fee,
o1.total_fee,
o1.`status`,
o1.order_type,
o1.qty
FROM
oddb.fg_order o1
LEFT JOIN oddb.fg_order_extra a1 ON o1.order_id = a1.order_id
WHERE
order_date BETWEEN @p_start
AND @p_end
AND `status` in ('closed','confirmed'))a
LEFT JOIN mddb.md_stores b ON (case when a.stores_id is null then a.member_id ELSE a.stores_id END) = b.stores_id
left JOIN mddb.md_area r1 on b.area_id=r1.area_code
group by
case when a.stores_id is null then a.member_id ELSE a.stores_id END ORDER BY order_qty desc
/*店东和消费者GMV*
set @p_start='2018-01-30'; -- 只需要修改此处的日期
SELECT
case when a.stores_id is null then a.member_mobile ELSE b.contact_tel END as '店东手机',
b.stores_name,
r1.path,
b.address,
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)*ifnull(qty,1) END) as '店东本人购买商品到手价',
sum(case when a.stores_id is not null then ifnull(a.discount_fee,0)*ifnull(qty,1) END) as '关联此店东消费者购买商品总到手价',
ifnull(sum(case when a.stores_id is null then ifnull(a.discount_fee,0)END),0) + ifnull(sum(case when a.stores_id is not null then ifnull(a.discount_fee,0) END),0) as 'GMV',
-- sum(case when a.stores_id is null then ifnull(a.total_fee,0)END) as '店东本人购买商品返款价',
-- sum(case when a.stores_id is not null then ifnull(a.total_fee,0) END) as '关联此店东消费者购买商品总返款价',
-- -- sum(case when a.stores_id is null then ifnull(a.discount_fee,0)
-- else ifnull(a.discount_fee,0) end
-- ) as '店铺总到手价',
-- count(case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东本人下单总量',
count(DISTINCT case when a.stores_id is not null then ifnull(a.member_id,0) END) '会员数',
count(
case when a.stores_id is null then ifnull(a.member_id,0)*ifnull(qty,1)
else ifnull(a.stores_id,0) end
) as '总订单'
from
(SELECT
o1.order_date,
o1.member_id,
o1.member_type_key,
o1.member_mobile,
a1.stores_id,
o1.discount_fee,
o1.total_fee,
o1.`status`,
o1.order_type,
o1.qty
FROM
oddb.fg_order o1
LEFT JOIN oddb.fg_order_extra a1 ON o1.order_id = a1.order_id
WHERE
order_date BETWEEN @p_start
AND concat(@p_start,' ','23:59:59')
AND `status` in ('closed','confirmed'))a
LEFT JOIN mddb.md_stores b ON (case when a.stores_id is null then a.member_id ELSE a.stores_id END) = b.stores_id
left JOIN mddb.md_area r1 on b.area_id=r1.area_code
group by
case when a.stores_id is null then a.member_id ELSE a.stores_id END
/*每个月订单统计，分店东和会员免费领和实惠领*/
SET @p_begin_date = '2017-12-01';
SET @p_end_date = '2017-12-31 23:59:59';
SELECT
b.stores_name '门店'
,c.path AS '所在地区'
,b.contact_tel AS '店东账号'
,CASE
WHEN b.stores_type_key = 'HYLX_01' THEN
'直营'
WHEN b.stores_type_key = 'HYLX_02' THEN
'联盟'
WHEN b.stores_type_key = 'HYLX_03' THEN
'加盟'
WHEN b.stores_type_key = 'HYLX_04' THEN
'创业'
END AS '合作模式'
,COUNT(CASE WHEN (a.member_id IS NOT NULL AND a.`status` = 'closed') THEN member_id END ) '门店订单'
,COUNT( CASE WHEN ( a.member_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee = a.discount_fee) THEN a.member_id END) AS '门店免费惠领'
,COUNT( CASE WHEN ( a.member_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee != a.discount_fee) THEN a.member_id END) AS '门店实惠领'
,COUNT( CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed' ) THEN consumer_id END) '会员订单'
,COUNT( CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee = a.total_fee ) THEN consumer_id END) AS '会员免费领'
,COUNT( CASE WHEN (a.consumer_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee != a.total_fee) THEN consumer_id END) AS '会员实惠领'
,COUNT( DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN consumer_id END) '会员数'
,SUM(CASE WHEN ( a.member_id IS NOT NULL AND a.`status` = 'closed') THEN IFNULL(a.discount_fee, 0.00) END) '门店订单优惠价'
,SUM(CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed') THEN IFNULL(a.discount_fee, 0.00) END) '会员订单优惠价'
,t1.assistant_name AS '所属业务员'
FROM
(
SELECT
a.order_id,
a.order_date,
a. STATUS,
a.member_id stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
NULL consumer_id,
a.member_id,
a.member_mobile
FROM
oddb.fg_order a
WHERE
a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT
a.order_id,
a.order_date,
a. STATUS,
b.stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
a.member_id consumer_id,
NULL member_id,
a.member_mobile
FROM
oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE
a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
LEFT JOIN mddb.md_assistant t1 ON t1.assistant_id = b.assistant_id
GROUP BY
a.stores_id
/*更新后的统计，一单多数量*/
SET @p_begin_date = '2018-04-01';
SET @p_end_date = '2018-04-30 23:59:59';
SELECT
b.stores_name '门店'
,c.path AS '所在地区'
,b.contact_tel AS '店东账号'
,CASE
WHEN b.stores_type_key = 'HYLX_01' THEN
'直营'
WHEN b.stores_type_key = 'HYLX_02' THEN
'联盟'
WHEN b.stores_type_key = 'HYLX_03' THEN
'加盟'
WHEN b.stores_type_key = 'HYLX_04' THEN
'创业'
END AS '合作模式'
,count(CASE WHEN (a.stores_id IS NOT NULL AND a.`status` = 'closed' and a.member_type_key='stores' ) THEN a.order_id END ) '门店订单'
,count(CASE WHEN ( a.stores_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee = a.total_fee and a.member_type_key='stores' ) THEN a.order_id END) AS '门店免费惠领'
,count( CASE WHEN ( a.stores_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee != a.total_fee and a.member_type_key='stores') THEN a.order_id END) AS '门店实惠领'
,count( CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed' and a.member_type_key='consumer') THEN a.order_id END) '会员订单'
,count( CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee = a.total_fee and a.member_type_key='consumer' ) THEN a.order_id END) AS '会员免费领'
,count( CASE WHEN (a.consumer_id IS NOT NULL AND a.`status` = 'closed' AND a.sale_fee != a.total_fee and a.member_type_key='consumer') THEN a.order_id END) AS '会员实惠领'
,COUNT( DISTINCT CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed') THEN consumer_id END) '会员数'
,SUM(CASE WHEN ( a.stores_id IS NOT NULL AND a.`status` = 'closed' and a.member_type_key='stores') THEN IFNULL(a.discount_fee,0.00)*ifnull(a.qty,1) END) '门店订单优惠价'
,SUM(CASE WHEN ( a.consumer_id IS NOT NULL AND a.`status` = 'closed') THEN IFNULL(a.discount_fee,0.00)*ifnull(a.qty,1) END) '会员订单优惠价'
,count(case when a.`STATUS`='closed' then a.member_id END) as '总订单'
,SUM(case when a.`STATUS`='closed' THEN IFNULL(a.discount_fee,0.00)*ifnull(a.qty,1) END) as '到手价'
,t1.assistant_name AS '所属业务员'
FROM
(
SELECT
a.order_id,
a.order_date,
a. STATUS,
a.member_id stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
NULL consumer_id,
a.member_mobile,
a.member_id,
a.member_type_key,
a.qty
FROM
oddb.fg_order a
WHERE
a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT
a.order_id,
a.order_date,
a.STATUS,
b.stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
a.member_id consumer_id,
a.member_mobile,
a.member_id,
a.member_type_key,
a.qty
FROM
oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE
a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
LEFT JOIN mddb.md_assistant t1 ON t1.assistant_id = b.assistant_id
where a.`STATUS`='closed'
GROUP BY
a.stores_id
/*运营每月要的店东，会员订，业务员单数据*/
set @p_begin_date='2017-07-01';
set @p_end_date='2017-07-31 23:59:59';
SELECT
b.stores_name
,c.path as '所在地区'
,b.contact_tel as '联系人'
,CASE WHEN b.stores_type_key='HYLX_01' then '直营'
WHEN b.stores_type_key='HYLX_02' then '联盟'
WHEN b.stores_type_key='HYLX_03' then '加盟'
WHEN b.stores_type_key='HYLX_04' then '创业' END as '合作模式'
,COUNT(CASE WHEN (a.member_id IS NOT NULL and a.`status` = 'closed') THEN member_id END ) '门店订单'
,COUNT(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'closed') THEN consumer_id END) '会员订单'
,COUNT(DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN consumer_id END) '会员数'
,COUNT(case when a.`status` = 'closed' then a.order_id END) '总订单'
-- ,SUM(CASE when IFNULL(a.sale_fee,0.00))
-- ,SUM(CASE WHEN (a.member_id IS NOT NULL and a.`status` = 'closed') then IFNULL(a.discount_fee,0.00) END) '门店订单优惠价'
-- ,SUM(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'closed') then IFNULL(a.discount_fee,0.00) END) '会员订单优惠价'
,t1.assistant_name as '所属业务员'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
,a.member_mobile
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
,a.member_mobile
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
LEFT JOIN mddb.md_assistant t1 on t1.assistant_id=b.assistant_id
GROUP BY a.stores_id
/* **************************************************************************************************** */
导入如下：
set @p_begin_date='2017-06-01';
set @p_end_date='2017-06-30 23:59:59';
set @d_format='%Y-%m-%d';
-- set @area='%深圳%';
SELECT
-- DATE_FORMAT(a.order_date,@d_format) '日期'
b.stores_name
-- substring_index(substring_index(c.path,',',2),',','-1') as '省'
-- ,substring_index(substring_index(c.path,',',3),',','-1') as '市'
,c.path as '所在地区'
,b.stores_no as '名店编号'
,b.contact_tel as '联系人'
-- CASE WHEN a.member_info IS NOT NULL
-- THEN substring(a.member_info,locate('mobile', a.member_info) + 9,11) end as '账号'
,CASE WHEN b.stores_type_key='HYLX_01' then '直营'
WHEN b.stores_type_key='HYLX_02' then '联盟'
WHEN b.stores_type_key='HYLX_03' then '加盟'
WHEN b.stores_type_key='HYLX_04' then '创业' END as '合作模式'
-- ,SUM(IFNULL(a.sale_fee,0.00)) gmv_amount
-- ,SUM(CASE WHEN a.status NOT IN('cancelled','processing','confirmed') THEN IFNULL(a.total_fee,0.00) ELSE 0.00 END) '返款金额'
,COUNT(CASE WHEN (a.member_id IS NOT NULL and a.`status` = 'closed') THEN member_id END ) '门店订单'
,COUNT(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'closed') THEN consumer_id END) '会员订单'
-- ,COUNT( ) '店东订单数量'
,COUNT(DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN consumer_id END) '会员数'
,COUNT(case when a.`status` = 'closed' then a.order_id END) '总订单'
-- ,SUM(CASE when IFNULL(a.sale_fee,0.00))
,SUM(CASE WHEN (a.member_id IS NOT NULL and a.`status` = 'closed') then IFNULL(a.discount_fee,0.00) END) '门店订单优惠价'
,SUM(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'closed') then IFNULL(a.discount_fee,0.00) END) '会员订单优惠价'
-- ,COUNT(case when a.sale_fee=a.total_fee AND THEN a.discount_fee END) '免费领订单数量'
--
-- ,COUNT(case when a.order_type='meitianhui' THEN a.order_type END) '自营订单数量'
--
-- ,COUNT(case when a.order_type='taobao' THEN a.order_type END) 'taobao订单数量'
--
-- ,count( CASE when (a.member_id IS NOT NULL and (b.stores_name like '%测试%' or b.stores_name LIKE '%体验%')) THEN a.member_id end) '测试订单'
-- ,count(CASE WHEN (a.member_id IS NOT NULL and a.`status` = 'cancelled') then a.order_id END) '门店取消订单'
--
-- ,count(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'cancelled') then a.order_id END) '会员取消订单'
--
,t1.assistant_name as '所属业务员'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
,a.member_mobile
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
,a.member_mobile
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
LEFT JOIN mddb.md_assistant t1 on t1.assistant_id=b.assistant_id
-- WHERE
-- c.path like @area
-- b.contact_tel in (4281590, 22235660)
GROUP BY a.stores_id order by DATE_FORMAT(a.order_date,@d_format)
-- GROUP BY substring_index(substring_index(c.path,',',3),',','-1')
-- ORDER BY DATE_FORMAT(a.order_date,@d_format) DESC;
------------------------------------------------------
---- 导出个别区域订单，注意，市可以没有店铺，但下面区市有的
SET @p_begin_date = '2017-06-01';
SET @p_end_date = '2017-06-30 23:59:59';
SELECT
c.path AS '所在地区'
,c.area_name
,COUNT(
CASE
WHEN a.`status` = 'closed' THEN
a.order_id
END
) '总订单' -- ,SUM(CASE when IFNULL(a.sale_fee,0.00))
FROM
(
SELECT
a.order_id,
a.order_date,
a. STATUS,
a.member_id stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
NULL consumer_id,
a.member_id,
a.member_mobile
FROM
oddb.fg_order a
WHERE
a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT
a.order_id,
a.order_date,
a. STATUS,
b.stores_id,
a.sale_fee,
a.discount_fee,
a.total_fee,
a.order_type,
a.member_id consumer_id,
NULL member_id,
a.member_mobile
FROM
oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE
a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN mddb.md_area c ON b.area_id = c.area_code
-- LEFT JOIN mddb.md_assistant t1 ON t1.assistant_id = b.assistant_id
WHERE
c.area_code in (370781,370783,371626,371581,370686,419001,469001,469002,469003,469005,469006,469007,469021,469022,469023,469024,469025,469026,469027,469028,469029,469030)
group by c.area_code
/*导出单个城市*/
set @p_begin_date='2017-06-01';
set @p_end_date='2017-06-30 23:59:59';
set @area='%衡水%';
select
a.order_date as '下单日期'
,a.order_no as '订单号'
,a.desc1 as '商品'
,a.member_type_key as '账号类型'
,'已完成' as '订单状态'
,a.settle_status
,c.path as '地区'
,b.stores_name as '店铺名称'
,a.sale_fee as '市场价'
,a.discount_fee as '返利金额'
,a.total_fee as '实际到手价'
from
(
SELECT a.order_id
,a.order_date
,a.order_no
,a.member_type_key
,a.status
,a.settle_status
,a.desc1
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
,a.member_mobile
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.order_no
,a.member_type_key
,a.status
,a.settle_status
,a.desc1
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
,a.member_mobile
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
WHERE
c.path like @area and a.status = 'closed'
/*领了么每月订单*/
/*定义导出订单起始时间*/
set @start_time='2017-07-01';
/*导出的结束时间，根据需要修改INTERVAL后面的值，如果是一天之内订单，保持默认值：-1即可*/
set @finished_time=date_sub(@start_time,INTERVAL -1 month);
SELECT
fo.order_no '订单号',
replace(REPLACE(REPLACE(foi.goods_code,',',''),CHAR(10),''),CHAR(13),'') '商品码',
replace(REPLACE(REPLACE(foi.goods_title,',',''),CHAR(10),''),CHAR(13),'') '商品',
fo.order_type '订单来源',
CASE
when s1.data_source='taobao' THEN '淘宝'
when s1.data_source='merchants' then '合作商' END as '商品来源',
replace(REPLACE(REPLACE(a1.account_no,',',''),CHAR(10),''),CHAR(13),'') as '淘宝会员名',
fo.item_num '订单',
foi.sale_price '市场价',
fo.total_fee '金额',
foi.settled_price '结算价',
DATE_FORMAT(
fo.order_date,
'%Y-%m-%d %T'
) '日期',
replace(REPLACE(REPLACE(foi.manufacturer,',',''),CHAR(10),''),CHAR(13),'') '厂商品牌',
fo.member_mobile '会员账号',
replace(REPLACE(REPLACE(fo.delivery_address,',',''),CHAR(10),''),CHAR(13),'') '配送地址',
replace(REPLACE(REPLACE(fo.contact_person,',',''),CHAR(10),''),CHAR(13),'') '联系人',
fo.contact_tel '联系电话',
CASE
when fo. STATUS='closed' then '已完成'
when fo.`status`='cancelled' then '超时取消'
ELSE '已确认' END as '订单状态',
-- '待结算' as '结算状态',
replace(REPLACE(REPLACE(fo.remark,',',''),CHAR(10),''),CHAR(13),'') as '用户备注',
fo.external_order_no '关联订单号',
replace(REPLACE(REPLACE(fo.biz_remark,',',''),CHAR(10),''),CHAR(13),'') '后台备注'
FROM
oddb.fg_order fo
INNER JOIN oddb.fg_order_item foi ON fo.order_id = foi.order_id
LEFT JOIN gddb.ps_goods s1 on foi.goods_id=s1.goods_id
LEFT JOIN mddb.md_member_external_account a1 on fo.member_id=a1.member_id
WHERE
fo.STATUS in ('closed','confirmed')
and fo.order_date >= @start_time
AND fo.order_date < @finished_time
/*泰安统计奖励会员*/
set @p_begin_date='2017-07-01';
set @p_end_date='2017-07-31 23:59:59';
set @d_format='%Y-%m-%d';
set @area='%泰安市%';
SELECT
a.member_mobile as '会员账号'
,c.path as '所在地区'
,COUNT(CASE WHEN (a.consumer_id IS NOT NULL and a.`status` = 'closed') THEN consumer_id END) '会员订单'
,b.contact_tel as '门店账号'
,b.stores_name as '门店名称'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
,a.member_mobile
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
,a.member_mobile
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
LEFT JOIN mddb.md_assistant t1 on t1.assistant_id=b.assistant_id
WHERE
c.path like @area
-- b.contact_tel in (4281590, 22235660)
GROUP BY a.consumer_id, a.stores_id ORDER BY member_mobile
-- GROUP BY substring_index(substring_index(c.path,',',3),',','-1')
-- ORDER BY DATE_FORMAT(a.order_date,@d_format) DESC;
/*惠易定4.0流水明细*/
SELECT
FROM_UNIXTIME(created_time, "%Y-%m-%d") '时间',
t1.receiver_state '省',
t1.receiver_city '城市',
u1.account_qty AS '会员店总数',
count(DISTINCT t1.user_id) '下单门店总数',
concat((count(DISTINCT t1.user_id) / u1.account_qty)*100,'%') AS '下单率',
count(tid) '订单数',
format(
sum(t1.payment) / count(tid),
3
) '客单价',
sum(t1.payment) '订单总价/惠易定系统2',
' ' AS '惠易定系统3.0',
sum(t1.payment) '合计流水',
'' AS '业务员数量',
'' AS '人均流水',
' ' AS '备注'
FROM
systrade_trade t1
LEFT JOIN (
SELECT
count(1) AS account_qty,
area
FROM
sysuser_user
WHERE
user_id NOT IN (
SELECT
user_id
FROM
sysuser_account
WHERE
disabled = 1
)
GROUP BY
substring_index(
SUBSTRING_INDEX(area, '/', 2),
'/' ,- 1
)
) u1 ON t1.receiver_city = substring_index(
SUBSTRING_INDEX(u1.area, '/', 2),
'/' ,- 1
)
WHERE
created_time BETWEEN UNIX_TIMESTAMP('2017-09-01')
AND UNIX_TIMESTAMP('2017-09-30 23:59:59')
AND t1.cancel_status ='NO_APPLY_CANCEL'
AND t1.receiver_state NOT LIKE '%澳门%'
GROUP BY
FROM_UNIXTIME(created_time, "%Y-%m-%d"),
t1.receiver_city
ORDER BY
FROM_UNIXTIME(created_time, "%Y-%m-%d")
/*惠易定4.0名店明细*/
SELECT
FROM_UNIXTIME(created_time,"%Y-%m") '时间',
t1.receiver_state '省',
t1.receiver_city '城市',
u1.supermarket '门店名称',
count(t1.tid) '下单数量',
format(sum(t1.payment)/count(t1.tid),3) '客单价',
sum(t1.payment) '订单总价/惠易定系统2',
' ' as '惠易定系统3',
' ' as '备注'
FROM
systrade_trade t1 INNER JOIN sysuser_user u1 on t1.user_id=u1.user_id
WHERE
created_time BETWEEN UNIX_TIMESTAMP('2017-09-01')
AND UNIX_TIMESTAMP('2017-09-30 23:59:59')
AND t1.cancel_status ='NO_APPLY_CANCEL'
AND t1.receiver_state NOT LIKE '%澳门%'
group by
FROM_UNIXTIME(created_time,"%Y-%m"),
t1.user_id
ORDER by FROM_UNIXTIME(created_time,"%Y-%m")
/*店东佣金，有效的领优惠订单*/
set @p_begin_date='2018-02-01';
set @p_end_date='2018-02-28 23:59:59';
SELECT
date_format(a.order_date,'%Y-%m-%d') as '日期'
,a.member_mobile '会员账号'
,replace(REPLACE(REPLACE(a.desc1,',',''),CHAR(10),''),CHAR(13),'') '商品名称'
,m1.goods_code '商品码'
,case when a.order_type='taobao' then '淘宝'
when a.order_type='meitianhui' then '自营'
else '会过' END as '订单类型'
,a.total_fee * ifnull(a.qty,1) '返款金额'
,a.discount_fee * ifnull(a.qty,1) '收到价'
,ifnull(a.qty,1) as '订单数量'
,replace(REPLACE(REPLACE( b.stores_name,',',''),CHAR(10),''),CHAR(13),'') '门店名称'
,b.contact_tel '店东账号'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
,a.member_mobile
,a.desc1
,a.qty
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
,a.member_mobile
,a.desc1
,a.qty
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
INNER JOIN oddb.fg_order_item m1 ON a.order_id=m1.order_id
where a.`status`='closed' order BY date_format(a.order_date,'%Y-%m-%d')
/*惠易定3.0流水明细*/
set @begin_time=UNIX_TIMESTAMP('2017-09-01');
set @end_time=UNIX_TIMESTAMP('2017-09-30 23:59:59');
SELECT
FROM_UNIXTIME(t.created_time,'%Y/%m/%d') '时间',
t.receiver_state '省',
t.receiver_city '城市',
amount.amt '会员店总数',
count(DISTINCT t.user_id) '下单门店数量',
concat(round(count(DISTINCT t.user_id)/amount.amt*100,3),'%') '下单率',
-- '' as '订单总金额2.0+3.0',
count(t.tid) '订单数',
round(sum(t.payment)/count(t.tid),3) '客单价',
sum(t.payment) '订单总金额'
FROM
systrade_trade AS t
LEFT JOIN (select state_id,city_id,count(1) as amt from hyd_convenient_store group by city_id) amount ON substring_index(SUBSTRING_INDEX(t.buyer_area,'/',2),'/',-1)=amount.city_id
WHERE
1 = 1
AND t.shop_type = 'hgj_hyd'
and t.status not in ('FAIL_GROUPON','TRADE_CLOSED','TRADE_CLOSED_BY_SYSTEM')
AND t.created_time BETWEEN @begin_time and @end_time
group by FROM_UNIXTIME(t.created_time,'%Y/%m/%d'), t.receiver_city
/*惠易定3.0名店明细*/
set @begin_time=UNIX_TIMESTAMP('2017-09-01');
set @end_time=UNIX_TIMESTAMP('2017-09-30 23:59:59');
SELECT
FROM_UNIXTIME(t.created_time,'%Y/%m') '时间',
t.receiver_state '省',
t.receiver_city '城市',
s.`name` as '门店名称',
count(t.tid) '下单数量',
round(sum(payment)/count(t.tid),3) '客单价',
sum(payment) '订单总金额'
FROM
systrade_trade AS t
LEFT JOIN hyd_convenient_store s ON t.user_id=s.user_id
WHERE
1 = 1
AND t.shop_type = 'hgj_hyd'
and t.status not in ('FAIL_GROUPON','TRADE_CLOSED','TRADE_CLOSED_BY_SYSTEM')
AND t.created_time BETWEEN @begin_time and @end_time
group by FROM_UNIXTIME(t.created_time,'%Y/%m'), t.user_id
/*
平台发红包金币的sql
*/
INSERT INTO `gddb`.`gc_activity_detail` (
`detail_id`,
`activity_id`,
`gift_type`,
`from_member_type_key`,
`from_member_id`,
`from_member_info`,
`to_member_type_key`,
`to_member_id`,
`to_member_info`,
`gift_value`,
`expired_date`,
`status`,
`modified_date`,
`created_date`,
`remark`
)
select
UUID() as detail_id,
uuid() as activity_id,
'gold' as gift_type,
'compnay' as from_member_type_key,
'10000001' as from_member_id,
'{\"company_name\":\"每天惠\"}' as from_member_info ,
'consumer' as to_member_type_key,
member_id as to_member_id,
null as to_member_info,
'500.00' as gift_value,
'2020-01-01 00:00:00' as expired_date,
'disable' as `status`,
now() as modified_date,
now() as created_date,
null as remark
from fddb.fd_member_asset where member_type_key='consumer'
/*
统计前几个月订单汇总
*/
set @p_begin_date='2017-10-01';
set @p_end_date='2017-10-31 23:59:59';
SELECT
COUNT(case when a.`status` = 'closed' then a.order_id END) '总订单'
,SUM(CASE WHEN a.status IN('closed') THEN IFNULL(a.sale_fee,0.00) ELSE 0.00 END) '订单金额'
,SUM(CASE WHEN a.status IN('closed') THEN IFNULL(a.total_fee,0.00) ELSE 0.00 END) '返款金额'
,COUNT(DISTINCT CASE WHEN a.stores_id IS NOT NULL THEN a.stores_id END) as '门店数量'
,COUNT(DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN a.consumer_id END) as '顾客数量'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date <= @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date <= @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
WHERE
c.path like '%德州%'
/*1月--10月份给定店东或地区的订单统计*/
set @p_begin_date='2017-01-01';
set @p_end_date='2017-10-31 23:59:59';
SELECT
DATE_FORMAT(a.order_date,'%Y-%m') as '月份'
,b.stores_name as '门店'
,c.path as '所在地区'
,b.contact_tel as '店东账号'
,COUNT(case when a.`status` ='closed' then a.order_id END) as '总订单'
,count(case when (a.`status`='closed' and a.sale_fee=a.total_fee) then a.order_id END) as '免费领订单量'
,count(case when (a.`status`='closed' and a.sale_fee != a.total_fee) then a.order_id END) as '实惠领订单量'
,COUNT(DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN a.consumer_id END) as '会员数'
,SUM(CASE WHEN a.status='closed' THEN IFNULL(a.total_fee,0.00) ELSE 0.00 END) as '返款金额'
,SUM(CASE WHEN a.status='closed' THEN IFNULL(a.sale_fee,0.00) ELSE 0.00 END) as '订单金额'
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,NULL consumer_id
,a.member_id
FROM oddb.fg_order a
WHERE a.member_type_key = 'stores'
AND a.order_date >= @p_begin_date
AND a.order_date <= @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.discount_fee
,a.total_fee
,a.order_type
,a.member_id consumer_id
,NULL member_id
FROM oddb.fg_order a
LEFT JOIN oddb.fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.order_date >= @p_begin_date
AND a.order_date <= @p_end_date
) a
LEFT JOIN mddb.md_stores b ON a.stores_id = b.stores_id
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
WHERE
b.contact_tel in ()
group by a.stores_id,DATE_FORMAT(a.order_date,'%Y-%m') ORDER BY DATE_FORMAT(a.order_date,'%Y-%m')
/*惠易定3.0分子公司导图片问题*/
SELECT
t.item_id,
t.title AS item_title,
iii.url AS item_m_url,
t.shop_id,
ss.shop_name,
t.price,
t.mkt_price,
t.remark,
sk.sku_id,
t.is_deleted,
sic.sold_quantity,
hi.min_order,
hi.can_return,
hi.store_alert,
hi.runtime_status,
hi.spec,
hi.suggest_price,
hi.product_date,
hi.origin,
hi.maker,
hi.guarantee_period,
hi.des,
hi.sale_sytle,
hi.warehouse,
hli.liangpin_attribute,
hli.liangpin_purchase,
hli.package_unit,
sis.approve_status,
(
CASE
WHEN t.examine = 0
AND (
t.examine_reason = ''
OR t.examine_reason IS NULL
) THEN
0
WHEN t.examine = 0
AND t.examine_reason IS NOT NULL
AND t.examine_reason != '' THEN
1
ELSE
2
END
) AS examine,
t.examine_reason,
il.id,
il.title,
il.cat_id,
il.barcode,
il.spec AS lib_spec,
il.origin,
il.brand,
il.brand_id,
il.image_id,
il.image,
il.unit,
il.STATUS AS lib_status,
il.type,
il.retail_price,
il.admin_id,
il.shop_id AS add_shop_id,
il.created_time,
il.last_modify_time,
il.COMMENT,
il.examine_reason AS lib_examine_reason,
hi.length,
hi.width,
hi.height,
hi.all_weight,
hi.real_weight,
hi.store_spec,
ii.l_url,
ii.m_url,
ii.s_url,
il.image_oss_url,
sc.cat_name,
sist.store,
hs.state_id,
hs.city_id,
hs.district_id,
t.is_deleted
FROM
sysitem_item t
LEFT JOIN hyd_item hi ON hi.item_id = t.item_id
LEFT JOIN image_image iii ON iii.image_id = t.image_default_id
LEFT JOIN sysshop_shop ss ON t.shop_id = ss.shop_id
LEFT JOIN sysitem_sku sk ON t.item_id = sk.item_id
LEFT JOIN huigujia_item_lib_v2 il ON il.barcode = t.bn
LEFT JOIN image_image ii ON ii.image_id = il.image_id
LEFT JOIN syscategory_cat sc ON sc.cat_id = il.cat_id
LEFT JOIN sysitem_item_count sic ON t.item_id = sic.item_id
LEFT JOIN sysitem_item_status sis ON sis.item_id = t.item_id
LEFT JOIN sysitem_item_store sist ON sist.item_id = t.item_id
LEFT JOIN hyd_supplier hs ON hs.shop_id = t.shop_id
LEFT JOIN hyd_liangpin_item hli ON hli.item_id = t.item_id
WHERE
1 = 1
AND il.id IS NOT NULL
AND hi.id IS NOT NULL
AND il.is_deleted = 0
AND t.is_deleted = 0
-- AND il. STATUS = 1
-- AND sis.approve_status = 'onsale'
AND t.shop_id IN (
SELECT
hsd.shop_id
FROM
hyd_supplier_dispatch AS hsd
WHERE
hsd.shop_id = ss.shop_id
AND (
hsd.dispatch_state_id = 410000
OR hsd.dispatch_state_id = 0
)
AND (
hsd.dispatch_city_id = 410700
OR hsd.dispatch_city_id = 0
)
)
-- AND sist.store > 0
ORDER BY
sic.sold_quantity DESC
/*
店东上一个月新注册和领取导出
*/
select
s1.stores_name as '门店'
,a1.path as '所在地区'
,s1.contact_tel as '店东账号'
,s1.contact_person as '店东名称'
,s1.address as '店东地址'
,COUNT( case when (o1.member_id IS NOT NULL and o1.`status` = 'closed') THEN ifnull(o1.member_id,0) END) as '店东订单'
from mddb.md_stores s1
LEFT JOIN
(select * from oddb.fg_order where order_date BETWEEN '2017-11-01' and '2017-11-30:23:59:59' and `status`='closed' and member_type_key='stores') o1 on s1.stores_id=o1.member_id
LEFT JOIN dwdb.dim_area_full a1 ON s1.area_id = a1.area_code
where s1.registered_date BETWEEN '2017-11-01' and '2017-11-30:23:59:59'
group by s1.stores_id order by 店东订单 desc
/*
店东激活及领
取领了么订单
*/
select
stores_name as '门店名称',
c.path as '所在地区',
b.contact_tel as '店东账号',
b.contact_person as '店东名称',
count(b.stores_id) as '11月总订单'
from mddb.md_stores b
INNER JOIN oddb.fg_order o1 on o1.member_id=b.stores_id AND member_type_key='stores' and `status`='closed'
LEFT JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
where ifnull(b.registered_date,b.created_date)
<'2017-11-01'
and b.stores_id in (select member_id from oddb.fg_order where member_type_key='stores' and order_date BETWEEN '2017-11-01' and '2017-11-30 23:59:59' and `status`='closed')
and b.stores_id not in (select member_id from oddb.fg_order where member_type_key='stores' and order_date<'2017-11-01' and `status`='closed')
group by o1.member_id
/*
新增会员
淘淘领，领取订单
*/
drop table if EXISTS tmp00100;
create table tmp00100
select
c1.consumer_id,
c1.mobile,
o1.member_id,
COUNT( case when (o1.member_id IS NOT NULL) THEN ifnull(o1.member_id,0) END) as order_cnt
from mddb.md_consumer c1
LEFT JOIN
(select * from oddb.fg_order where order_date BETWEEN '2017-11-01' and '2017-11-30:23:59:59' and `status`='closed' and member_type_key='consumer') o1 on c1.consumer_id=o1.member_id
where c1.registered_date BETWEEN '2017-11-01' and '2017-11-30 23:59:59' group by c1.consumer_id ;
ALTER TABLE `tmp00100` ADD PRIMARY KEY (`consumer_id`);
select
t1.mobile as '消费者账号',
a1.path as '所在地址',
s1.stores_name as '选定门店名称',
s1.contact_tel as '对应店东账号',
t1.order_cnt as '11月总订单'
from tmp00100 t1
LEFT JOIN oddb.fg_order_extra e1 on t1.consumer_id=e1.consumer_id
LEFT JOIN mddb.md_stores s1 on e1.stores_id=s1.stores_id
LEFT JOIN dwdb.dim_area_full a1 ON s1.area_id = a1.area_code
group by t1.consumer_id order by t1.order_cnt desc
/**
店东购买商品到手价及关联此店东消费者购买商品到手价，及店铺计算的总的到手价
**/
set @p_start='2018-01-22'; -- 只需要修改此处的日期
SELECT
case when a.stores_id is null then a.member_mobile ELSE b.contact_tel END as '店东手机',
b.stores_name,
r1.path,
b.address,
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)END) as '店东本人购买商品到手价',
sum(case when a.stores_id is not null then ifnull(a.discount_fee,0) END) as '关联此店东消费者购买商品总到手价',
sum(case when a.stores_id is null then ifnull(a.total_fee,0)END) as '店东本人购买商品返款价',
sum(case when a.stores_id is not null then ifnull(a.total_fee,0) END) as '关联此店东消费者购买商品总返款价',
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)
else ifnull(a.discount_fee,0) end
) as '店铺总到手价',
count(case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东本人下单总量',
count(case when a.stores_id is not null then ifnull(a.member_id,0) END) '关联此店东消费者下单总量',
count(
case when a.stores_id is null then ifnull(a.member_id,0)
else ifnull(a.stores_id,0) end
) as '总订单'
from
(SELECT
o1.order_date,
o1.member_id,
o1.member_type_key,
o1.member_mobile,
a1.stores_id,
o1.discount_fee,
o1.total_fee,
o1.`status`,
o1.order_type
FROM
oddb.fg_order o1
LEFT JOIN oddb.fg_order_extra a1 ON o1.order_id = a1.order_id
WHERE
order_date BETWEEN @p_start
AND concat(@p_start,' ','23:59:59')
AND `status` = 'closed')a
LEFT JOIN mddb.md_stores b ON (case when a.stores_id is null then a.member_id ELSE a.stores_id END) = b.stores_id
left JOIN mddb.md_area r1 on b.area_id=r1.area_code
group by
case when a.stores_id is null then a.member_id ELSE a.stores_id END order by 店铺总到手价 desc
/*
运营平台淘淘领报表
*/
set @p_begin_date='2017-12-01';
set @p_end_date='2017-12-24 23:59:59';
set @p_area_id='ebb55635-bb51-11e5-a4b3-00163e0009c6';
SELECT c.district_name
,COUNT(a.order_id) order_qty
,SUM(IFNULL(a.sale_fee,0.00)) gmv_amount
,COUNT(CASE WHEN a.status = 'closed' and a.discount_fee<>0 THEN a.order_id END) actual_order_qty
,COUNT(CASE WHEN a.status = 'closed' and a.discount_fee=0 THEN a.order_id END) free_order_qty
,SUM(CASE WHEN a.status IN('received','closed') THEN IFNULL(a.total_fee,0.00) ELSE 0.00 END) paid_amount
,COUNT(DISTINCT a.stores_id) store_qty
,COUNT(DISTINCT CASE WHEN a.consumer_id IS NOT NULL THEN consumer_id END) consumer_qty
FROM (
SELECT a.order_id
,a.order_date
,a.status
,a.member_id stores_id
,a.sale_fee
,a.total_fee
,a.discount_fee
,NULL consumer_id
FROM odsdb.oddb_fg_order a
WHERE a.member_type_key = 'stores'
AND a.status IN('confirmed','received','closed')
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
UNION
SELECT a.order_id
,a.order_date
,a.status
,b.stores_id
,a.sale_fee
,a.total_fee
,a.discount_fee
,a.member_id consumer_id
FROM odsdb.oddb_fg_order a
LEFT JOIN odsdb.oddb_fg_order_extra b ON a.order_id = b.order_id
WHERE a.member_type_key = 'consumer'
AND a.status IN('confirmed','received','closed')
AND a.order_date >= @p_begin_date
AND a.order_date < @p_end_date
) a
INNER JOIN odsdb.md_stores b ON a.stores_id = b.stores_id
INNER JOIN dwdb.dim_area_full c ON b.area_id = c.area_code
WHERE c.city_id = @p_area_id
GROUP BY c.district_name
ORDER BY COUNT(a.order_id) DESC
/*领有惠订单导出*/
/*定义导出订单起始时间*/
set @start_time='2017-06-01';
/*导出的结束时间，根据需要修改INTERVAL后面的值，如果是一天之内订单，保持默认值：-1即可*/
set @finished_time=date_sub(@start_time,INTERVAL -1 month);
SELECT
fo.order_no '订单号',
replace(REPLACE(REPLACE(foi.goods_code,',',''),CHAR(10),''),CHAR(13),'') '商品码',
case when fo.delivery_address like '%省%' then SUBSTRING_INDEX(fo.delivery_address,' ',1) END as '省',
case when fo.delivery_address like '%省%' then substring_index(SUBSTRING_INDEX(fo.delivery_address,' ',2),' ' ,-1) END as '市',
case when fo.delivery_address like '%省%' then substring_index(SUBSTRING_INDEX(fo.delivery_address,' ',3),' ' ,-1) END as '区/县',
replace(REPLACE(REPLACE(foi.goods_title,',',''),CHAR(10),''),CHAR(13),'') '商品',
case when fo.order_type='taobao' then '淘宝'
when fo.order_type='huoguo' then '会过'
when fo.order_type='meitianhui' then '自营' END as '订单来源',
CASE when s1.data_source='taobao' THEN '淘宝'
when s1.data_source='merchants' then '合作商'
when s1.data_source='hsrj' then '花生日记'
when s1.data_source='meitianhui' then '自营'
when s1.data_source='huiguo' then '会过' END as '商品来源',
replace(REPLACE(REPLACE(a1.account_no,',',''),CHAR(10),''),CHAR(13),'') as '淘宝会员名',
fo.item_num '订单数量',
foi.sale_price '市场价',
fo.total_fee '金额',
foi.settled_price '结算价',
DATE_FORMAT(
fo.order_date,
'%Y-%m-%d %T'
) '日期',
replace(REPLACE(REPLACE(foi.manufacturer,',',''),CHAR(10),''),CHAR(13),'') '厂商品牌',
fo.member_mobile '会员账号',
-- replace(REPLACE(REPLACE(fo.delivery_address,',',''),CHAR(10),''),CHAR(13),'') '配送地址',
replace(REPLACE(REPLACE(fo.contact_person,',',''),CHAR(10),''),CHAR(13),'') '联系人',
fo.contact_tel '联系电话',
CASE
when fo. STATUS='closed' then '已完成'
when fo.`status`='cancelled' then '超时取消'
ELSE '已确认' END as '订单状态',
-- '待结算' as '结算状态',
replace(REPLACE(REPLACE(fo.remark,',',''),CHAR(10),''),CHAR(13),'') as '用户备注',
fo.external_order_no '关联订单号',
replace(REPLACE(REPLACE(fo.biz_remark,',',''),CHAR(10),''),CHAR(13),'') '后台备注'
FROM
oddb.fg_order fo
INNER JOIN oddb.fg_order_item foi ON fo.order_id = foi.order_id
LEFT JOIN gddb.ps_goods s1 on foi.goods_id=s1.goods_id
LEFT JOIN mddb.md_member_external_account a1 on fo.member_id=a1.member_id
WHERE
fo.STATUS in ('closed','confirmed')
and fo.order_date >= @start_time
AND fo.order_date < @finished_time
/*店东及其关联的消费者下单数量和金额*/
set @p_start='2018-01-29'; -- 只需要修改此处的日期
SELECT
case when a.stores_id is null then a.member_mobile ELSE b.contact_tel END as '店东手机',
b.stores_name,
r1.path,
b.address,
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)END) as '店东本人购买商品到手价',
sum(case when a.stores_id is not null then ifnull(a.discount_fee,0) END) as '关联此店东消费者购买商品总到手价',
sum(case when a.stores_id is null then ifnull(a.total_fee,0)END) as '店东本人购买商品返款价',
sum(case when a.stores_id is not null then ifnull(a.total_fee,0) END) as '关联此店东消费者购买商品总返款价',
sum(case when a.stores_id is null then ifnull(a.discount_fee,0)
else ifnull(a.discount_fee,0) end
) as '店铺总到手价',
count(case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东本人下单总量',
count(case when a.stores_id is not null then ifnull(a.member_id,0) END) '关联此店东消费者下单总量',
count(DISTINCT case when a.stores_id is null then ifnull(a.member_id,0)END) as '店东数量',
count(DISTINCT case when a.stores_id is not null then ifnull(a.member_id,0) END) '消费者数量',
sum(case when a.stores_id is null then ifnull(a.sale_fee,0)
else ifnull(a.sale_fee,0) end
) as '总预付价',
count(
case when a.stores_id is null then ifnull(a.member_id,0)
else ifnull(a.stores_id,0) end
) as '总订单'
from
(SELECT
o1.order_date,
o1.member_id,
o1.member_type_key,
o1.member_mobile,
a1.stores_id,
o1.discount_fee,
o1.total_fee,
o1.sale_fee,
o1.`status`,
o1.order_type
FROM
oddb.fg_order o1
LEFT JOIN oddb.fg_order_extra a1 ON o1.order_id = a1.order_id
WHERE
order_date BETWEEN @p_start
AND concat(@p_start,' ','23:59:59')
AND `status` in ('closed'))a
LEFT JOIN mddb.md_stores b ON (case when a.stores_id is null then a.member_id ELSE a.stores_id END) = b.stores_id
left JOIN mddb.md_area r1 on b.area_id=r1.area_code
group by
case when a.stores_id is null then a.member_id ELSE a.stores_id END order by 店铺总到手价 desc
/*统计每月返款记录*/
SELECT
sum(o2.amount) as '返款',
o1.order_type '订单类型'
FROM
oddb.fg_order o1
INNER JOIN (
SELECT
out_trade_no,
amount
FROM
fddb.fd_transactions_result
WHERE
transaction_date BETWEEN '2018-01-01'
AND '2018-01-31 23:59:59'
AND data_source = 'SJLY_03'
AND order_type_key IN (
'DDLX_10','DDLX_07','DDLX_21')
AND business_type_key IN (
'JYLX_09'
)
AND payment_way_key = 'ZFFS_05'
) o2 ON o1.order_no = o2.out_trade_no
GROUP BY
o1.order_type
