Amazon Redshift編～Redshiftをはじめてみよう！パート④～

1copy users from 's3://<region-specific-bucket-name>/tickit/allusers_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|';2copy venue from 's3://<region-specific-bucket-name>/tickit/venue_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|';3copy category from 's3://<region-specific-bucket-name>/tickit/category_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|';4copy date from 's3://<region-specific-bucket-name>/tickit/date2008_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|';5copy event from 's3://<region-specific-bucket-name>/tickit/allevents_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|' 6	timeformat 'YYYY-MM-DD HH:MI:SS';78copy listing from 's3://<region-specific-bucket-name>/tickit/listings_pipe.txt' CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' delimiter '|';9copy sales from 's3://<region-specific-bucket-name>/tickit/sales_tab.txt'CREDENTIALS 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'10	  delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS';

1-- Get definition for the sales table.2SELECT *    3FROM pg_table_def    4WHERE tablename = 'sales';

1-- Find total sales on a given calendar date.2SELECT sum(qtysold) 3FROM   sales, date 4WHERE  sales.dateid = date.dateid 5AND    caldate = '2008-01-05';

1-- Find top 10 buyers by quantity.2SELECT firstname, lastname, total_quantity 3FROM   (SELECT buyerid, sum(qtysold) total_quantity4        FROM  sales5        GROUP BY buyerid6        ORDER BY total_quantity desc limit 10) Q, users7WHERE Q.buyerid = userid8ORDER BY Q.total_quantity desc;

1-- Find events in the 99.9 percentile in terms of all time gross sales.2SELECT eventname, total_price 3FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 4       FROM (SELECT eventid, sum(pricepaid) total_price5             FROM   sales6             GROUP BY eventid)) Q, event E7       WHERE Q.eventid = E.eventid8       AND percentile = 19ORDER BY total_price desc;