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 / basic_information.sql
Created June 28, 2012 01:25
Book-Crossing Dataset
-- 1.1.1 登録ユーザー数, アクティブユーザー数 --
td query -w -d book_crossing_dataset "
SELECT t1.cnt AS all_users, t2.cnt AS active_users, ROUND(t2.cnt/t1.cnt*100) AS active_rate
FROM
(
SELECT COUNT(distinct user_id) as cnt, 1 AS one
FROM users
) t1
JOIN
(
@doryokujin
doryokujin / file0.sql
Last active May 13, 2021 12:38
『Treasure Data でアクセスログ分析の限界に挑む』その② 〜アクセスに基づいたユーザーセグメントの作成 月次編（アクティビティ）〜 ref: https://qiita.com/doryokujin/items/605158dc61e378685265
WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去 */
@doryokujin
doryokujin / td_time_range.sql
Last active April 23, 2019 08:14
boolean TD_TIME_RANGE(int/long unix_timestamp, int/long/string start_time, int/long/string end_time [, string default_timezone = 'UTC'])
-- （例）2013年のデータを取得
SELECT ... WHERE TD_TIME_RANGE(time, "2013-01-01 JST", "2014-01-01 JST")
@doryokujin
doryokujin / gist:1258927
Created October 3, 2011 11:33
manage rvm and gem using fabric
# -*- coding: utf-8 -*-
#
# install: http://morgangoose.com/blog/2011/01/06/using-the-parallel-branch-of-fabric/
# document: http://docs.fabfile.org/en/1.2.0/index.html
#
import os
from fabric.api import *
from fabric.decorators import *
@doryokujin
doryokujin / file0.sql
Last active December 7, 2018 05:36
『Treasure Data でアクセスログ分析の限界に挑む』その② 〜アクセスに基づいたユーザーセグメントの作成 月次編（頻度）〜 ref: https://qiita.com/doryokujin/items/d4899269a0c0aa7db6ea
TD_DATE_TRUNC('day',time,'JST') AS access_day
@doryokujin
doryokujin / file0.sql
Created December 6, 2018 01:32
『Treasure Data でアクセスログ分析の限界に挑む』その④ 〜アクセスに基づいたユーザーセグメントの作成 月次編（時間帯）〜 ref: https://qiita.com/doryokujin/items/750e3f322c2bc00371ac
/* TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' */
/*
SELECT target_month, segment, COUNT(1) AS cnt
FROM
(
*/
SELECT target_month,
CASE
WHEN 50<=ratio_freq_sleeping OR 50<=ratio_pv_sleeping THEN 'sleeping'
WHEN 50<=ratio_freq_morning OR 50<=ratio_pv_morning THEN 'morning'
@doryokujin
doryokujin / file0.sql
Last active December 5, 2018 08:36
『Treasure Data でアクセスログ分析の限界に挑む』その③ 〜アクセスに基づいたユーザーセグメントの作成 月次編（平日/土日）〜 ref: https://qiita.com/doryokujin/items/89392c4cc0bc00c71629
TD_TIME_FORMAT(time,'u','JST') # 1(月曜日) 〜 7（日曜日）
@doryokujin
doryokujin / file0.sql
Last active November 27, 2018 02:23
『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜 ref: https://qiita.com/doryokujin/items/773f6008e420c7f3260d
# 基準日 = '2018-11-23 11:11:00'
[2018-11-22 00:00:00, 2018-11-23 00:00:00) # 日次
[2018-11-12 00:00:00, 2018-11-19 00:00:00) # 週次
[2018-10-01 00:00:00, 2018-11-01 00:00:00) # 月次
@doryokujin
doryokujin / book_status.sql
Created June 28, 2012 08:07
Book-Crossing Dataset
-- 3.1.1 出版年別分布 --
td query -w -d book_crossing_dataset "
SELECT year, cnt
FROM
(
SELECT 1975 AS year, COUNT(year_of_publication) AS cnt
FROM books
WHERE year_of_publication < 1970
UNION ALL
@doryokujin
doryokujin / badcase1.sql
Last active July 3, 2017 06:05
SELECT * FROM table1
WHERE
col1 < 100
OR col2 is TRUE
AND TD_TIME_RANGE(time, '2015-11-01')