SQL

–Common Table Expressions (not called Temporary table, many thanks to YZ to correct me, refer to http://www.sql-server-performance.com/2015/common-table-expressions-cte-developers/) with T as ( select Product_ID,max(case when MEDIA_LEG_RESULT=’T’ then 1 else 0 end) as T_leg from detail  data1 where NVL(disconnect_reason,’1′) NOT IN (‘SPAM’,’DUPLICATE’) and connect_date_datetime BETWEEN to_date(‘2016-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and to_date(‘2016-08-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and data1.Location_id=1 group … Read more

SQL today For each comm_id, there are several items. For each item, there is a fulfillment method. For each item, there is a row in the data. comm_id Item_name Method 100 Socks A 100 Book A 100 pen B 21 clothes B   We want to get a count of each method for each comm_id. … Read more

最基本的常用sql code 今天用了一天R和SQL，SQL是在Data Ware House (DW) 跑的，具体的流程十几分钟一学就会，主要是要会写SQL code. 我是自学的，贴一段工作中用的最基本的，初学者可以先从最基本的和理解这段code开始，学会最常用的where 和 left join。 select distinct tabled.comm_id, o1.component_id as sic1, o2.component_id as sic2 from contact_detail tabled left join (select comm_id,component_id  from components where type_code=111) o1 on tabled.comm_id=o1.comm_id left join (select comm_id,component_id  from components where type_code=222) o2 on tabled.comm_id=o2.comm_id where disconnect_date between  to_date(‘2016-08-16 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and to_date(‘2016-08-31 … Read more