Redshift SQL 基本语句

Shortcut key

ctrl + enter -> run from ‘;’ to ‘;’
cmd + left(right) -> jump to head(tail) of the line
alt + left(right) -> jump to head(tail) of the words

1 select ｜ choose the data you need

1) where, limit

select * from processed.fact_dau_snapshot 
where date>='2015-12-15' and date<='2015-12-28'
limit 20;

2) as, in, group by, sum, count, order by, desc

select snsid,sum(amount) as sum_amount,count(1) as times_bought from processed.fact_revenue
where app in ('ffs.global.prod','ffs.amazon.prod')
group by snsid
order by sum_amount desc
limit 20;

3) *, case, row_number(),

select snsid,amount as first_amount,case when amount=0.99 then 1 when amount>0.99 and amount<=4.99 then 2 else 3 end payment_class from(select snsid,amount from
(select *,row_number() over (partition by snsid order by ts) as row_num from processed.fact_revenue)
where row_num=1)
limit 20;

4) join (left join | right join | full outer join) on

select vip_level,count(A.snsid) from
(select snsid from processed.fact_dau_snapshot where app='ffs.global.prod' and date='2015-12-25')A
left join
(select snsid,vip_level from processed.dim_user)B
on A.snsid=B.snsid
group by 1
order by 2 desc;

5) nullif(), trunc(), json_extract_path_text(), json_extract_array_element_text()

select date,item_id,item_type,count(1),sum(json_extract_path_text(properties,'rc_out')) as rc_out from
(select properties,trunc(ts) as date,json_extract_path_text(json_extract_array_element_text(json_extract_path_text(properties,'item_change'),0),'item_id') as item_id,
json_extract_path_text(json_extract_array_element_text(json_extract_path_text(properties,'item_change'),0),'type') as item_type from
(select *,json_extract_path_text(properties,'real_action') as real_action from public.events_raw where event='rc_transaction' and ts>='2015-12-18')
where real_action='wheel2_buy')
group by date,item_id,item_type;

6) union | union all, split_part()

select id,count(1) from
(select * from
(select split_part(json_extract_path_text(properties,'ids'),',',1) as id from public.events_raw
where event='NewOrderRefresh' and trunc(ts)='2016-01-25' and app='ffs.global.prod' ) 
union all
(select split_part(json_extract_path_text(properties,'ids'),',',2) as id from public.events_raw
where event='NewOrderRefresh' and trunc(ts)='2016-01-25' and app='ffs.global.prod')
)
group by 1
;

7）ts <-> timestamp

SELECT TIMESTAMP 'epoch' + nullif(json_extract_path_text(properties,'garden_unlock_ts'),'')::int * INTERVAL '1 second' 
from public.events_raw 
where event='garden_level'
limit 20;

http://www.postgresql.org/docs/9.2/static/functions-datetime.html
http://my.oschina.net/Kenyon/blog/57188

2 export | export data to local

WbExport -type=text
         -delimiter=';'
         -header=true
         -file='/Users/zhisheng/Desktop/mytemp1.txt'
         -table=newtable;
select * from ...;

3 write tables

create table pm.temp1 as
(select * from ...);commit;

4 rc_out report sql script

-- New rc_transaction report sql script
WbExport -type=text
         -delimiter=',' 
         -header=true
         -file='/Users/zhisheng/Desktop/mytemp1.txt'
         -table=newtable;
select YESTERDAY.action,YESTERDAY.field1,TODAY.action as action_today,sum_rc_today,count_rc_today,TODAY.price,TODAY.field1 as field1_today,(sum_rc_today-sum_rc)/(sum_rc*1.0) as comments from
(select * from
(select action,field1,sum(rc) as sum_rc_today,count(rc) as count_rc_today,sum(rc)/(count(rc)*1.0) as price from
(select date, snsid, level,nvl(action1, action2) as action, location,
  nvl(nvl(rc_in, rc_out),0) as rc,
    case when rc_in>0 then 1 when rc_out>0 then -1 else 0 end flow,
--subtype indicates item/achievement/quest/gallery - supplements action to know what field1 and field2 refer to
case
  when item!='' then 'item'
  when quest_id!='' then 'quest'
  else NULL end subtype,
--field1 indicates item_name/quest_id 
case
  when item!='' then json_extract_path_text(item, 'item_name')
  when quest_id!='' then quest_id
  when action1!='' then action1
  else action2 end field1,
--field2 provides additional identifiers (e.g. task_id) if necessary
case
  when quest_id!='' then nullif(json_extract_path_text(properties, 'task_id'),'')::int
  else NULL end field2,
--single out quantity of items if possible
case
  when item!='' then nullif(json_extract_path_text(item, 'quantity'),'')::int
  else NULL end quantity
from
(select snsid,trunc(ts) as date, properties,
json_extract_path_text (properties,'level')::int as level,
          nullif(json_extract_path_text (properties,'real_action'),'') as action1,
                    nullif(json_extract_path_text (properties,'action'),'') as action2,
          json_extract_array_element_text(json_extract_path_text (properties,'item_change'),0) as item,
          json_extract_path_text (properties,'quest_id') as quest_id,
          json_extract_path_text (properties,'location') as location,
                    nullif(json_extract_path_text (properties,'rc_out'),'')::int as rc_out,
                    nullif(json_extract_path_text (properties,'rc_in'),'')::int as rc_in
from public.events_raw
where snsid not in (select snsid from processed.cheaters)
and snsid not in (select snsid from processed.cheaters_new)
and ts>='2015-12-1 00:00:00' and ts<'2015-12-2 00:00:00'
and event='rc_transaction'
and app='ffs.global.prod'
))
where flow=-1 and level>=12 and level<28
group by action,field1
order by sum_rc_today desc
)
where price<100
limit 20) TODAY
full outer join
 -- New rc_transaction
(select * from
(select action,field1,sum(rc) as sum_rc,count(rc) as count_rc,sum(rc)/(count(rc)*1.0)::float as price from
(select date, snsid, level,nvl(action1, action2) as action, location,
  nvl(nvl(rc_in, rc_out),0) as rc,
    case when rc_in>0 then 1 when rc_out>0 then -1 else 0 end flow,
--subtype indicates item/achievement/quest/gallery - supplements action to know what field1 and field2 refer to
case
  when item!='' then 'item'
  when quest_id!='' then 'quest'
  else NULL end subtype,
--field1 indicates item_name/quest_id 
case
  when item!='' then json_extract_path_text(item, 'item_name')
  when quest_id!='' then quest_id
  when action1!='' then action1
  else action2 end field1,
--field2 provides additional identifiers (e.g. task_id) if necessary
case
  when quest_id!='' then nullif(json_extract_path_text(properties, 'task_id'),'')::int
  else NULL end field2,
--single out quantity of items if possible
case
  when item!='' then nullif(json_extract_path_text(item, 'quantity'),'')::int
  else NULL end quantity
from
(select snsid,trunc(ts) as date, properties,
json_extract_path_text (properties,'level')::int as level,
          nullif(json_extract_path_text (properties,'real_action'),'') as action1,
                    nullif(json_extract_path_text (properties,'action'),'') as action2,
          json_extract_array_element_text(json_extract_path_text (properties,'item_change'),0) as item,
          json_extract_path_text (properties,'quest_id') as quest_id,
          json_extract_path_text (properties,'location') as location,
                    nullif(json_extract_path_text (properties,'rc_out'),'')::int as rc_out,
                    nullif(json_extract_path_text (properties,'rc_in'),'')::int as rc_in
from public.events_raw
where snsid not in (select snsid from processed.cheaters)
and snsid not in (select snsid from processed.cheaters_new)
and ts>='2015-11-30 00:00:00' and ts<'2015-12-1 00:00:00'
and event='rc_transaction'
and app='ffs.global.prod'
))
where flow=-1 and level>=12 and level<28
group by action,field1
order by sum_rc desc
)
where price<100
limit 20) YESTERDAY
on TODAY.field1=YESTERDAY.field1
order by TODAY.sum_rc_today desc;

TIPS

1) Always write comments, comments go after –
2) Always count before export. 60w rows of data is the top limit
3) Run short script then add script to it
4) Validate data before you use it
5) Try to search before ask; stack overflow helps a lot

Links

http://www.postgresql.org/docs/9.3/static/index.html
http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html