なるテク

なるほど！なテクニックや技術をまとめるブログ

postgresqlのコマンドライン操作

コマンドライン操作の開始

psql コマンドを使用する。

https://www.postgresql.jp/document/9.3/html/app-psql.html

# 対話的問い合わせの開始
$ psql -d database_name

# テーブル一覧の表示
==>\d

サンプルデータの操作

Sample Databases - PostgreSQL wiki

の

pgFoundry: Sample Databases: Project Filelist

にある world データを使ってみる。

$ wget -P /tmp pgfoundry.org/frs/download.php/527/world-1.0.tar.gz
$ cd /tmp
$ tar zxvf world-1.0.tar.gz
dbsamples-0.1/
dbsamples-0.1/world/
dbsamples-0.1/world/world.sql
dbsamples-0.1/world/README
$ ls
dbsamples-0.1  world-1.0.tar.gz

sqlのwindow関数のframe

window関数のframeについてまとめたい。 なおwindow関数はpostgresql, prestoでは利用可能で、MySQLでは利用できない。

今回使ってみるのはpresto 0.152である。

WITH data AS (
SELECT *
FROM (VALUES
('2016-12-01', '/',                    100),
('2016-12-01', '/category',            100),
('2016-12-01', '/faq',                 100),
('2016-12-02', '/',                    100),
('2016-12-02', '/faq',                 100),
('2016-12-03', '/',                    100),
('2016-12-03', '/category',            100),
('2016-12-04', '/category/technology', 100)
) AS temp (date, path, pv)
)

SELECT
  date,
  path,
  pv,
  SUM(pv) OVER(PARTITION BY path) AS "ページごとのpv合計",
  SUM(pv) OVER(PARTITION BY path ORDER BY date) AS "ページごとのその日までのpv合計",
  SUM(pv) OVER(PARTITION BY path ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS "ページごとの前日までのpv合計"
FROM data
ORDER BY date, path
;
date path pv ページごとのpv合計 ページごとのその日までのpv合計 ページごとの前日までのpv合計
2016-12-01 / 100 300 100
2016-12-01 /category 100 200 100
2016-12-01 /faq 100 200 100
2016-12-02 / 100 300 200 100
2016-12-02 /faq 100 200 200 100
2016-12-03 / 100 300 300 200
2016-12-03 /category 100 200 200 100
2016-12-04 /category/technology 100 100 100
  • frameの設定には ROWS RANGE の二種類ある。

参考リンク

redashコードリーディング：開発環境の準備

redash 0.12.0について

vagrant ssh
sudo apt-get purge python-pip
wget https://bootstrap.pypa.io/get-pip.py
sudo python get-pip.py
exit
  • pipが動く状態で再度provision
# hostマシン上で
vagrant halt
vagrant provision
./bin/vagrant_ctl.sh start
  • 以下で admin admin と入力すると、

f:id:noymer:20161204181923p:plain

  • ログイン出来た！

f:id:noymer:20161204182010p:plain

prestoクエリサンプル：window関数内で集約関数を利用する

TreasureData by IDC Frontier presto version 0.152.3の場合。 ＊基本的に標準sqlに従っているので他のクエリにも応用可能なはず。

クエリ

あるサイトの日ごとの各ページのpv数データから、全期間でのpv数をページごとに出し、pv数が多い順にランキング付けして並べる。

WITH data AS (
SELECT *
FROM (VALUES
('2016-12-01', '/',                    100),
('2016-12-01', '/category',            100),
('2016-12-01', '/faq',                 100),
('2016-12-02', '/',                    100),
('2016-12-02', '/faq',                 100),
('2016-12-03', '/',                    100),
('2016-12-03', '/category',            100),
('2016-12-04', '/category/technology', 100)
) AS temp (date, path, count)
)

SELECT
  path,
  SUM(count) AS count,
  RANK() OVER(ORDER BY SUM(count) DESC) AS rank
FROM data
GROUP BY path
ORDER BY rank
;

結果

path count rank
/ 300 1
/faq 200 2
/category 200 2
/category/technology 100 4

説明

  • まず VALUES を使用してサンプルデータを用意した。この方法はTreasureDataでも推奨されている。

    Presto Query FAQs | Treasure Data

    ある日付 date にあるページ path のpv count がいくつだったか？というデータである。

  • ページごとのpv数をだすのには GROUP BY path で SUM(count) とすればよい。

  • ランキング付けするにはwindow関数の RANK を SUM(count) に対して使いたいが、それはそのまま RANK() OVER(ORDER BY SUM(count) DESC) とすればよい。

    6.15. Window Functions — Presto 0.158 Documentation

  • このときポイントなのは、 GROUP BY に使うのは path だけでよいということである。 RANK()... は集約関数でないが、GROUP BY に含めなくてよい。

  • そして以下のように RANK() OVER(ORDER BY path) とした場合でも、やはり RANK()... はGROUP BY に含めなくてよい。

WITH data AS (
...
),

SELECT
  path,
  SUM(count) AS count,
  RANK() OVER(ORDER BY path) AS rank
FROM data
GROUP BY path
ORDER BY rank
;
path count rank
/ 300 1
/category 200 2
/category/technology 100 2
/faq 200 4
  • window関数は集約も含めた他のコラムの処理のあとに行われるということなのだろう。