hao123 百度品专 按品类 计算 下单数量 商品数量 下单金额？
SELECT * FROM t_tag_source WHERE s_name='hao123';
+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+
| s_name | s_fname | s_level | tag_id | tag_fid | author | updatetime | level_1_id | level_2_id | level_3_id | category |
+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+
| hao123 | 外部合作 | 1 | 63 | -2 | kevinlu | 1346155861 | 63 | 0 | 0 | 2 |
+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+
mysql> SELECT * FROM t_tag_source where s_name like 'SEM%';
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
| s_name | s_fname | s_level | tag_id | tag_fid | author | updatetime | level_1_id | level_2_id | level_3_id | category |
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
| SEM百度华南 | 外部合作 | 1 | 62 | -2 | winkyye | 1345600539 | 62 | 0 | 0 | 2 |
| SEM百度品专 | 外部合作 | 1 | 69 | -2 | winkyye | 1346833461 | 69 | 0 | 0 | 2 |
| SEM百度华东 | 外部合作 | 1 | 70 | -2 | winkyye | 1346909566 | 70 | 0 | 0 | 2 |
| SEM百度华北 | 外部合作 | 1 | 79 | -2 | winkyye | 1347869030 | 79 | 0 | 0 | 2 |
| SEM搜狗品专 | 外部合作 | 1 | 81 | -2 | winkyye | 1348656505 | 81 | 0 | 0 | 2 |
| SEM奇虎品专 | 外部合作 | 1 | 84 | -2 | winkyye | 1350356677 | 84 | 0 | 0 | 2 |
| SEM360搜索 | 外部合作 | 1 | 85 | -2 | winkyye | 1351222732 | 85 | 0 | 0 | 2 |
| SEM百度华中 | 外部合作 | 1 | 109 | -2 | stevietao | 1354687847 | 109 | 0 | 0 | 2 |
| SEM百度 | 外部合作 | 1 | 128 | -2 | stevietao | 1363922244 | 128 | 0 | 0 | 2 |
| SEM360品专 | 外部合作 | 1 | 252 | -2 | wheatycai | 1398070719 | 252 | 0 | 0 | 2 |
| SEM百度华东 | 百度SEM | 2 | 12801 | 128 | stevietao | 1363922291 | 128 | 1 | 0 | 2 |
| SEM百度华南 | 百度SEM | 2 | 12802 | 128 | stevietao | 1363922300 | 128 | 2 | 0 | 2 |
| SEM百度华北 | 百度SEM | 2 | 12803 | 128 | stevietao | 1363922330 | 128 | 3 | 0 | 2 |
| SEM百度华中 | 百度SEM | 2 | 12804 | 128 | stevietao | 1363922344 | 128 | 4 | 0 | 2 |
| SEM百度深圳 | SEM百度 | 2 | 12805 | 128 | stevietao | 1367116894 | 128 | 5 | 0 | 2 |
| SEM百度网盟 | SEM百度 | 2 | 12806 | 128 | ken | 1371611403 | 128 | 6 | 0 | 2 |
| SEM易迅活动 | SEM百度 | 2 | 12807 | 128 | ken | 1372989539 | 128 | 7 | 0 | 2 |
| SEM百度测试 | SEM百度 | 2 | 12811 | 128 | 闄跺洯 | 1389871496 | 128 | 11 | 0 | 2 |
| SEM | 可乐项目 | 2 | 24812 | 248 | 钄＄喒 | 1392704926 | 248 | 12 | 0 | 2 |
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
19 rows in set (0.01 sec)
从结果看出，hao123 tag_id 63 SEM百度品专 tag_id 69 他们都是level_1 级别的
--百度品专
select COUNT(distinct OId) as 下单数量,
c1name as 大类,
SUM(Qty) as 商品数量,
SUM(OFee) as 下单金额
from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT ISNULL(tr.ext_11002, '') as OTag,
td.Fbusiness_deal_id as OId,
CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate,
tr.Ftrade_buy_num as Qty,
(tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee,
ISNULL(u.Ficson_uid,0) as UId,
(case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id,
prod.ProductName, c1.c1name
from t_deal td
left join t_trade tr on td.Fdeal_id = tr.fdeal_id
left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id
LEFT JOIN etl0_icson_product..product prod ON tr.Fitem_local_product_id = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
--WHERE tr.fdeal_id = 1404002103
--where ISNULL(tr.ext_11002, '') LIKE '22102000173%'
where td.Fdeal_gen_time >= '2014-03-01 00:00:00.000' AND td.Fdeal_gen_time < '2014-03-02 00:00:00.000' and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8))t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
SUBSTRING (t.Otag, 1, 2)='69' )y
group by c1name
--hao123
select COUNT(distinct OId) as 下单数量,
c1name as 大类,
SUM(Qty) as 商品数量,
SUM(OFee) as 下单金额
from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT ISNULL(tr.ext_11002, '') as OTag,
td.Fbusiness_deal_id as OId,
CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate,
tr.Ftrade_buy_num as Qty,
(tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee,
ISNULL(u.Ficson_uid,0) as UId,
(case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id,
prod.ProductName, c1.c1name
from t_deal td
left join t_trade tr on td.Fdeal_id = tr.fdeal_id
left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id
LEFT JOIN etl0_icson_product..product prod ON tr.Fitem_local_product_id = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
--WHERE tr.fdeal_id = 1404002103
--where ISNULL(tr.ext_11002, '') LIKE '22102000173%'
where td.Fdeal_gen_time >= '2014-09-03 00:00:00.000' AND td.Fdeal_gen_time < '2014-09-04 00:00:00.000' and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8))t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
SUBSTRING (t.Otag, 1, 2)='63' )y
group by c1name
2014年6月份之前的数据计算方式
--百度品专
select COUNT(distinct OId) as 下单数量,
c1name as 大类,
SUM(Qty) as 商品数量,
SUM(OFee) as 下单金额
from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT d.otag as OTag,
m.soid as OId,
CONVERT(VARCHAR, m.orderdate, 112) OrderDate,
d.quantity as Qty,
(d.price * d.quantity) as OFee,
m.customersysno as UId,
m.siteno as wh_id,
prod.ProductName, c1.c1name
from etl0_icson_cs..so_master m INNER JOIN
etl0_icson_cs..so_item d ON m.sysno = d.sosysno
LEFT JOIN etl0_icson_product..product prod ON d.productsysno = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
where m.orderdate >= '2014-03-01' AND m.orderdate < '2014-04-01') t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
SUBSTRING (t.Otag, 1, 2)='69' )y
group by c1name
--hao123
select COUNT(distinct OId) as 下单数量,
c1name as 大类,
SUM(Qty) as 商品数量,
SUM(OFee) as 下单金额
from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT d.otag as OTag,
m.soid as OId,
CONVERT(VARCHAR, m.orderdate, 112) OrderDate,
d.quantity as Qty,
(d.price * d.quantity) as OFee,
m.customersysno as UId,
m.siteno as wh_id,
prod.ProductName, c1.c1name
from etl0_icson_cs..so_master m INNER JOIN
etl0_icson_cs..so_item d ON m.sysno = d.sosysno
LEFT JOIN etl0_icson_product..product prod ON d.productsysno = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
where m.orderdate >= '2014-03-01' AND m.orderdate < '2014-04-01') t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
SUBSTRING (t.Otag, 1, 2)='63' )y
group by c1name
