Hao123 what is the order amount for Baidu products based on categories?

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 | external cooperation | 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 Baidu South China | external cooperation | 1 | 62 |-2 | winkyye | 1345600539 | 62 | 0 | 0 | 2 |
| SEM Baidu product | external cooperation | 1 | 69 |-2 | winkyye | 1346833461 | 69 | 0 | 0 | 2 |
| SEM Baidu East China | external cooperation | 1 | 70 |-2 | winkyye | 1346909566 | 70 | 0 | 0 | 2 |
| SEM Baidu North China | external cooperation | 1 | 79 |-2 | winkyye | 1347869030 | 79 | 0 | 0 | 2 |
| SEM sogou product | external cooperation | 1 | 81 |-2 | winkyye | 1348656505 | 81 | 0 | 0 | 2 |
| SEM Qihoo product | external cooperation | 1 | 84 |-2 | winkyye | 1350356677 | 84 | 0 | 0 | 2 |
| Sem360 search | external cooperation | 1 | 85 |-2 | winkyye | 1351222732 | 85 | 0 | 0 | 2 |
| SEM Baidu Huazhong | external cooperation | 1 | 109 |-2 | stevietao | 1354687847 | 109 | 0 | 0 | 2 |
| SEM Baidu | external cooperation | 1 | 128 |-2 | stevietao | 1363922244 | 128 | 0 | 0 | 2 |
| Sem360 product | external cooperation | 1 | 252 |-2 | wheatycai | 1398070719 | 252 | 0 | 0 | 2 |
| SEM Baidu East China | Baidu SEM | 2 | 12801 | 128 | stevietao | 1363922291 | 128 | 1 | 0 | 2 |
| SEM Baidu South China | Baidu SEM | 2 | 12802 | 128 | stevietao | 1363922300 | 128 | 2 | 0 | 2 |
| SEM Baidu North China | Baidu SEM | 2 | 12803 | 128 | stevietao | 1363922330 | 128 | 3 | 0 | 2 |
| SEM Baidu Huazhong | Baidu SEM | 2 | 12804 | 128 | stevietao | 1363922344 | 128 | 4 | 0 | 2 |
| SEM Baidu Shenzhen | SEM Baidu | 2 | 12805 | 128 | stevietao | 1367116894 | 128 | 5 | 0 | 2 |
| SEM Baidu wangmeng | SEM Baidu | 2 | 12806 | 128 | Ken | 1371611403 | 128 | 6 | 0 | 2 |
| SEM Yi Xun activity | SEM Baidu | 2 | 12807 | 128 | Ken | 1372989539 | 128 | 7 | 0 | 2 |
| SEM Baidu test | SEM Baidu | 2 | 12811 | 128 | 1389871496 | 128 | 11 | 0 | 2 |
| SEM | cola item | 2 | 24812 | 248 | California $ cola | 1392704926 | 248 | 12 | 0 | 2 |
+ ------------- + ---------- + --------- + -------- + --------- + ----------- + ------------ +
19 rows in SET (0.01 Sec)

From the results, we can see that hao123 tag_id 63 SEM Baidu product specialized tag_id 69 are all level_1-level

-- Baidu product Special Select count (distinct OID) as order quantity, c1name as category, sum (qty) as commodity quantity, sum (oamount) as order amount 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 oexample, isnull (U. ficson_uid, 0) as uid, (case when TD. fdeal_source in (1969, 8) then else td. fsite_id end) as wh_id, prod. productname, c1.c1namefrom t_deal TD left join t_trade TR on TD. fdeal_id = tr. fdeal_idleft join etl_users .. t_user_buyer U on u. fwg_uid = TD. fbuyer_idleft join etl0_icson_product .. product prod on TR. fitem_local_product_id = prod. sysnoleft join etl0_icson_product .. category1 C1 on c1.sysno = prod. c1sysno -- where tr. fdeal_id = 1404002103 -- where isnull (TR. ext_11002, '') Like '000000' where TD. fdeal_gen_time> = '2017-03-01 00:00:00. 000 'and TD. fdeal_gen_time <'2017-03-02 00:00:00. 000 'and TD. fdeal_property3 = 64 and TD. fdeal_source not in (2, 3, 8) twhere -- substring (T. otag, 0, charindex ('-', otag) = '000000' substring (T. otag, 1, 2) = '69') ygroup by c1name -- hao123select count (distinct OID) as order quantity, c1name as category, sum (qty) as commodity quantity, sum (OSS) as order amount 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 oexample, isnull (U. ficson_uid, 0) as uid, (case when TD. fdeal_source in (1969, 8) then else td. fsite_id end) as wh_id, prod. productname, c1.c1namefrom t_deal TD left join t_trade TR on TD. fdeal_id = tr. fdeal_idleft join etl_users .. t_user_buyer U on u. fwg_uid = TD. fbuyer_idleft join etl0_icson_product .. product prod on TR. fitem_local_product_id = prod. sysnoleft join etl0_icson_product .. category1 C1 on c1.sysno = prod. c1sysno -- where tr. fdeal_id = 1404002103 -- where isnull (TR. ext_11002, '') Like '000000' where TD. fdeal_gen_time> = '2017-09-03 00:00:00. 000 'and TD. fdeal_gen_time <'2017-09-04 00:00:00. 000 'and TD. fdeal_property3 = 64 and TD. fdeal_source not in (2, 3, 8) twhere -- substring (T. otag, 0, charindex ('-', otag) = '000000' substring (T. otag, 1, 2) = '63') ygroup by c1name

Data calculation method before March

-- Baidu product Special Select count (distinct OID) as order quantity, c1name as category, sum (qty) as commodity quantity, sum (oamount) as order amount 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 ostrap, M. customersysno as uid, M. siteno as wh_id, prod. productname, c1.c1namefrom etl0_icson_cs .. so_master m inner join etl0_icson_cs .. so_item D on M. sysno = D. sosysnoleft join etl0_icson_product .. product prod on D. productsysno = prod. sysnoleft join etl0_icson_product .. category1 C1 on c1.sysno = prod. c1sysnowhere M. orderdate> = '2014-03-01 'and M. orderdate <'2014-04-01 ') twhere -- substring (T. otag, 0, charindex ('-', otag) = '000000' substring (T. otag, 1, 2) = '69') ygroup by c1name -- hao123select count (distinct OID) as order quantity, c1name as category, sum (qty) as commodity quantity, sum (OSS) as order amount 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 ostrap, M. customersysno as uid, M. siteno as wh_id, prod. productname, c1.c1namefrom etl0_icson_cs .. so_master m inner join etl0_icson_cs .. so_item D on M. sysno = D. sosysnoleft join etl0_icson_product .. product prod on D. productsysno = prod. sysnoleft join etl0_icson_product .. category1 C1 on c1.sysno = prod. c1sysnowhere M. orderdate> = '2014-03-01 'and M. orderdate <'2014-04-01 ') twhere -- substring (T. otag, 0, charindex ('-', otag) = '000000' substring (T. otag, 1, 2) = '63') ygroup by c1name

　　

 

 

Hao123 what is the order amount for Baidu products based on categories?