Help us understand the problem. What is going on with this article?

CSV形式のデータをSQLを使って解析する

More than 5 years have passed since last update.

q を利用すると、CSVやTSV形式のファイルに対して直接SQLを発行できます。

インストール手順

qを使ったデータ解析の例

以下の様な受注データのCSV (ファイル名はorders.csv) が手元にあった時。

OrderId,OrderDate,ProductID,UserID,Quantity,TotalPrice
10000005,2015/2/13,100,10000,1,10000
10000006,2015/2/13,101,10001,2,2000
10000007,2015/2/13,102,10002,3,6000
10000008,2015/2/13,100,10003,1,10000
10000009,2015/2/13,101,10004,2,2000
10000010,2015/2/14,100,10005,3,30000
10000011,2015/2/14,101,10006,1,2000
10000012,2015/2/14,102,10007,2,4000
10000013,2015/2/14,100,10008,3,30000
10000014,2015/2/14,101,10009,1,1000

売上(TotalPrice)が10000円以上のデータを抽出したい

$ q -H -d',' "SELECT * FROM ./orders.csv WHERE TotalPrice >= 10000"

10000005,2015/2/13,100,10000,1,10000
10000008,2015/2/13,100,10003,1,10000
10000010,2015/2/14,100,10005,3,30000
10000013,2015/2/14,100,10008,3,30000

-H はヘッダ行をスキップするという意味。
-d でデリミタ指定 (CSVなので「,」を指定)

日毎の売上を集計したい

$ q -H -d',' "SELECT OrderDate, SUM(TotalPrice) FROM ./orders.csv GROUP BY OrderDate"

2015/2/13,30000
2015/2/14,67000

商品売上ランキングを出したい

q -H -d',' "SELECT ProductId, SUM(Quantity), SUM(TotalPrice) AS total FROM ./orders.csv GROUP BY ProductId Order BY total DESC"

100,8,80000
102,5,10000
101,6,7000

データ解析がはかどりますね。

nishio-dens
Railsエンジニア。JS書いたり、インフラ屋やったりもします。
https://densan-labs.net
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした