引き続きSQLについて学んで行きましょう。

必要なものをimportして、関数を作っておきます。

In [1]:
import sqlite3
import pandas as pd
con = sqlite3.connect("sakila.db")

#前のレクチャーのものをそのまま使って構いません。

def sql_to_df(sql_query):

    df = pd.read_sql(sql_query, con)

    return df

データをまとめる関数の一覧です。group byと一緒に使うと便利です。

  • AVG() - 平均を返します。
  • COUNT() - 個数を数えます。
  • FIRST() - 最初の値を返します。
  • LAST() - 最後の値を返します。
  • MAX() - 最大値を返します。
  • MIN() - 最小値を返します。
  • SUM() - 合計を返します。

具体的な使い方を見ていきましょう。

In [2]:
# お客さんの人数を数えます。
query = ''' SELECT COUNT(customer_id)
            FROM customer; '''

sql_to_df(query).head()
Out[2]:
COUNT(customer_id)
0 599

基本的な使い方は、以下の様な感じです。

SELECT 列名, まとめる関数(列名)
FROM テーブル名
WHERE ・・・

ワイルドカード

SQLは、所謂ワイルドカードを使った検索も出来ます。

ワイルドカードには、いくつか種類があります。

ワイルドカード 説明
% 0個以上の文字を置き換えるものと解釈されます。
_ 1文字だけをワイルドカードにします。
[文字のリスト] いくつか、文字の候補を並べることが出来ます。

実際の例を見てみましょう。

In [7]:
# まずは、% から

# 名前がMから始まるお客さん全員
query = ''' SELECT *
            FROM customer
            WHERE first_name LIKE 'M%' ; '''

sql_to_df(query).head()
Out[7]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 1 1 MARY SMITH MARY.SMITH@sakilacustomer.org 5 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
1 7 1 MARIA MILLER MARIA.MILLER@sakilacustomer.org 11 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
2 9 2 MARGARET MOORE MARGARET.MOORE@sakilacustomer.org 13 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
3 21 1 MICHELLE CLARK MICHELLE.CLARK@sakilacustomer.org 25 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
4 30 1 MELISSA KING MELISSA.KING@sakilacustomer.org 34 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
In [12]:
# 次に、_ 

# 名字が4文字で、INGで終わる人
query = ''' SELECT *
            FROM customer
            WHERE last_name LIKE '_ING' ; '''

sql_to_df(query).head()
Out[12]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 30 1 MELISSA KING MELISSA.KING@sakilacustomer.org 34 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29

[文字のリスト] を使った例です。

注意

SQLにも若干違い(方言)があります。

MySQLでは、

WHERE value LIKE '[文字リスト]%'

SQLiteでは、

WHERE value GLOB '[文字リスト]*'

In [13]:
# 実際にやってみましょう。

# 名前がAまたはBで始まる人をとってきます。
query = ''' SELECT *
            FROM customer
            WHERE first_name GLOB '[AB]*' ; '''

sql_to_df(query).head()
Out[13]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 4 2 BARBARA JONES BARBARA.JONES@sakilacustomer.org 8 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
1 14 2 BETTY WHITE BETTY.WHITE@sakilacustomer.org 18 1 2006-02-14 22:04:36.000 2011-09-14 18:10:28
2 29 2 ANGELA HERNANDEZ ANGELA.HERNANDEZ@sakilacustomer.org 33 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
3 31 2 BRENDA WRIGHT BRENDA.WRIGHT@sakilacustomer.org 35 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
4 32 1 AMY LOPEZ AMY.LOPEZ@sakilacustomer.org 36 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29

ORDER BY を使った並べ替え

ORDER BYを使うと、結果を並べ替えることが出来ます。デフォルトでは、昇順(小さいものから大きいものへ)に並びます。DESCというキーワードを使うと、これを逆の降順にすることが出来ます。

SELECT 列名
FROM テーブル名
ORDER BY 列名 [DESC]

In [14]:
# すべてのお客さんを、その名字で並べて返します。
query = ''' SELECT *
            FROM customer
            ORDER BY last_name ; '''

sql_to_df(query).head()
Out[14]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 505 1 RAFAEL ABNEY RAFAEL.ABNEY@sakilacustomer.org 510 1 2006-02-14 22:04:37.000 2011-09-14 18:10:42
1 504 1 NATHANIEL ADAM NATHANIEL.ADAM@sakilacustomer.org 509 1 2006-02-14 22:04:37.000 2011-09-14 18:10:42
2 36 2 KATHLEEN ADAMS KATHLEEN.ADAMS@sakilacustomer.org 40 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
3 96 1 DIANA ALEXANDER DIANA.ALEXANDER@sakilacustomer.org 100 1 2006-02-14 22:04:36.000 2011-09-14 18:10:30
4 470 1 GORDON ALLARD GORDON.ALLARD@sakilacustomer.org 475 1 2006-02-14 22:04:37.000 2011-09-14 18:10:41
In [15]:
# 順番を逆にします。
query = ''' SELECT *
            FROM customer
            ORDER BY last_name DESC; '''

sql_to_df(query).head()
Out[15]:
customer_id store_id first_name last_name email address_id active create_date last_update
0 28 1 CYNTHIA YOUNG CYNTHIA.YOUNG@sakilacustomer.org 32 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29
1 413 2 MARVIN YEE MARVIN.YEE@sakilacustomer.org 418 1 2006-02-14 22:04:37.000 2011-09-14 18:10:40
2 402 1 LUIS YANEZ LUIS.YANEZ@sakilacustomer.org 407 1 2006-02-14 22:04:37.000 2011-09-14 18:10:39
3 318 1 BRIAN WYMAN BRIAN.WYMAN@sakilacustomer.org 323 1 2006-02-14 22:04:37.000 2011-09-14 18:10:37
4 31 2 BRENDA WRIGHT BRENDA.WRIGHT@sakilacustomer.org 35 1 2006-02-14 22:04:36.000 2011-09-14 18:10:29

GROUP BY で結果をまとめる

GROUP BYと関数を一緒に使うと、複数の行(レコード)をまとめることが出来ます。

SELECT 列名, 関数(列名)
FROM テーブル名
WHERE ・・・
GROUP BY 列名;

In [17]:
# お店ごとのお客さんの数を数えます。

query = ''' SELECT store_id , COUNT(customer_id)
            FROM customer
            GROUP BY store_id; '''

sql_to_df(query).head()
Out[17]:
store_id COUNT(customer_id)
0 1 326
1 2 273