Skip to content

Instantly share code, notes, and snippets.

View doryokujin's full-sized avatar

doryokujin doryokujin

40 followers · 1 following
  • Treasure Data
View GitHub Profile
@doryokujin
doryokujin / badcase1'.sql
Last active July 3, 2017 06:05
-- 元のクエリ
col1 < 100 OR col2 is TRUE AND TD_TIME_RANGE(time, '2015-11-01')
-- 上のクエリは AND が優先的に考慮されるため， 以下のクエリと同義になります。
-- OR によって左右に分解されたこのクエリでは， TD_TIME_RANGE があるにも関わらず， (col1 < 100) の判定のために全件スキャンになります！
(col1 < 100) OR (col2 is TRUE AND TD_TIME_RANGE(time, '2015-11-01'))　
-- クエリ全体に TIME_RANGE 制約を利かす場合は明示的に () を使いましょう！
-- 以下のクエリでは TIME_RANGE 制約内のデータセットで (col1 < 100 OR col2 is TRUE) が判定されます。
(col1 < 100 OR col2 is TRUE) AND TD_TIME_RANGE(time, '2015-11-01')
@doryokujin
doryokujin / goodcase1.sql
Last active July 3, 2017 06:04
SELECT * FROM (VALUES (1, 'apple'), (2, 'banana')) as fruit(id, name);
# このクエリでは， 以下のデータセットがクエリ内で生成され使用されています。
# id | name
#-----+--------
# 1 | apple
# 2 | banana
@doryokujin
doryokujin / cohort.yml
Created April 11, 2017 07:19
in:
type: google_analytics
json_key_content: |
{
// from Google API Service Account's Private Key
"type": "service_account",
"project_id": "ga-to-td",
"private_key_id": "18f4c*****",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgI*****",
...
@doryokujin
doryokujin / digdag1.sh
Created January 10, 2017 01:39
doryokujin :: ~/mydag » digdag run mydag.dig 1 ↵
2017-01-10 10:38:06 +0900: Digdag v0.9.1
2017-01-10 10:38:08 +0900 [WARN] (main): Using a new session time 2017-01-10T00:00:00+00:00.
2017-01-10 10:38:08 +0900 [INFO] (main): Using session /Users/doryokujin/mydag/.digdag/status/20170110T000000+0000.
2017-01-10 10:38:08 +0900 [INFO] (main): Starting a new session project id=1 workflow name=mydag session_time=2017-01-10T00:00:00+00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+setup): echo>: start 2017-01-10T00:00:00+00:00
start 2017-01-10T00:00:00+00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+disp_current_date): echo>: 2017-01-10 00:00:00 +00:00
2017-01-10 00:00:00 +00:00
2017-01-10 10:38:09 +0900 [INFO] (0018@+mydag+repeat): for_each>: {order=[first, second, third], animal=[dog, cat]}
@doryokujin
doryokujin / window.lag.sql
Last active November 4, 2016 01:09
SELECT
time, goods_id, amount,
--第2引数で遡るステップ数を指定--
LAG(amount, 1) OVER (PARTITION BY goods_id ORDER BY time) AS lag1,
--第2引数を指定しない時は1つ前のamountを取得--
LAG(amount) OVER (PARTITION BY goods_id ORDER BY time) AS lag2,
--第3引数で存在しない場合のデフォルト値を指定できる--
LAG(amount, 1, 0) OVER (PARTITION BY goods_id ORDER BY time) AS lag3,
--第2引数で先のステップ数を指定--
@doryokujin
doryokujin / slemma_uniques.sql
Created October 31, 2016 04:15
SELECT
TD_TIME_FORMAT(time,'yyyy-MM-dd') AS d,
COUNT(1) AS pv,
COUNT(DISTINCT td_client_id) AS uu
FROM
pageviews
GROUP BY
TD_TIME_FORMAT(time,'yyyy-MM-dd')
ORDER BY
d
@doryokujin
doryokujin / survival_analysis.sql
Last active October 17, 2016 07:11
SELECT
duration_per_q,
cancel_num,
censored_num,
survive_num,
km_stat_seed,
EXP(SUM(LN(km_stat_seed))OVER(ORDER BY duration_per_q ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS km_stat
FROM
(
SELECT
@doryokujin
doryokujin / td_ec_goods_sales_monthly.csv
Created September 5, 2016 07:11
month member_id goods_id sub_category category amount sales
2015-02-01 2084822 593901 Musical Instruments Movies and Music and Games 1 2839
2015-02-01 1400295 594055 Baby Clothing and Shoes and Jewelry 1 1000
2015-02-01 2084837 586278 Books Books and Audible 1 2839
2015-02-01 111442 593933 Wedding Registry Home and Garden and Tools 1 2848
2016-04-01 2068068 668145 Car/Vehicle Electronics and GPS Automotive and Industrial 2 2000
2016-04-01 591009 664872 Video Games Movies and Music and Games 1 2839
2015-12-01 1932731 646845 Trade In Your Electronics Electronics and Computers 1 1000
2015-12-01 905489 645071 Power and Hand Tools Home and Garden and Tools 1 2362
2015-12-01 745362 621351 Digital Games Movies and Music and Games 1 3791
@doryokujin
doryokujin / td_js_log_monthly.csv
Last active September 5, 2016 06:07
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 8 columns, instead of 3. in line 6.
month,td_client_id,td_url,td_title,td_referrer,td_browser,td_language,cnt
2016-08-01,831ca1,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,,IE,ja-jp,2
2016-08-01,09240e,https://www.treasuredata.com/jp/partnerships,パートナー企業 - Treasure Data,https://www.treasuredata.com/jp/,IE,ja-jp,1
2016-08-01,a435df,https://www.treasuredata.com/jp/jp_download_corporate_overview_str/,TREASURE DMPの概要 - Treasure Data,https://www.facebook.com,Firefox,ja,1
2016-08-01,58df61,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,http://nt-lsoxap.housefoods.co.jp/mail/MailPreView.do,IE,ja-jp,2
2016-08-01,8f4fd4,https://www.treasuredata.com/jp/press_release/20160407_private_dmp_solution_treasure_dmp,プライベートDMPソリューション”TREASURE(トレジャー) DMP(ディーエムピー)”を 4月より提供開始 - プレスリリース - Treasure Data,https://www.treasuredata.com/jp/about,IE,ja-jp,1
2016-08-01,4edea6,http://get.treasuredata.com/JP-Download-Corporate-Overview.html
@doryokujin
doryokujin / data_connector_for_ga_basics.yml
Last active August 31, 2016 07:42
in:
type: google_analytics
json_key_content: |
{
// from Google API Service Account's Private Key
"type": "service_account",
"project_id": "ga-to-td",
"private_key_id": "18f4c*****",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgI*****",
...