SQL - 高度なSELECT文: 新たな目でデータを見る(グループ化(GROUP BY), 合計(SUM), 平均(AVG), 最大(MAX), 最小(MIN), 並べ替え(ORDER BY), 降順(DESC))

Head First SQL
頭とからだで覚えるSQLの基本
(オライリージャパン)
Lynn Beighley (著)
佐藤 直生 (監訳), 松永 多苗子 (翻訳)

開発環境

• OS X Mavericks - Apple、たまにFreeBSD 10(OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python 3.4(プログラミング言語)
• SQLite (RDBMS(Relational Database Management System))

Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley(著)、 佐藤 直生 (監訳)、 松永 多苗子 (翻訳)、オライリージャパン)の6章(高度なSELECT文: 新たな目でデータを見る)、エクササイズ(p.265)を解いてみる。

エクササイズ(p.265)

コード(BBEdit, Emacs)

sample263.py

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import sqlite3

connection = sqlite3.connect('chapter6.sqlite')
cursor = connection.cursor()

def p_all(table):
    cursor.execute("""SELECT * FROM {0}""".format(table))
    print(tuple(map(lambda header: header[0], cursor.description)))
    p(table)

def p(msg):
    print(msg)
    for row in cursor.fetchall():
        print(row)

p_all('cookie_sales')

cursor.execute("""
SELECT first_name, sales
FROM cookie_sales
ORDER BY first_name
""")

p('ORDER BY first_name')

connection.close()

入出力結果(Terminal, IPython)

$ ./sample265.py
('ID', 'first_name', 'sales', 'sale_date')
cookie_sales
(1, 'Lindsey', 32.02, '2007-03-12')
(2, 'Nicole', 26.53, '2007-03-12')
(3, 'Britney', 11.25, '2007-03-12')
(4, 'Ashley', 18.96, '2007-03-12')
(5, 'Lindsey', 9.16, '2007-03-11')
(6, 'Nicole', 1.52, '2007-03-11')
(7, 'Britney', 43.21, '2007-03-11')
(8, 'Ashley', 8.05, '2007-03-11')
(9, 'Lindsey', 17.62, '2007-03-10')
(10, 'Nicole', 24.19, '2007-03-10')
(11, 'Britney', 3.4, '2007-03-10')
(12, 'Ashley', 15.21, '2007-03-10')
(13, 'Lindsey', 0, '2007-03-09')
(14, 'Nicole', 31.99, '2007-03-09')
(15, 'Britney', 2.58, '2007-03-09')
(16, 'Ashley', 0, '2007-03-09')
(17, 'Lindsey', 2.34, '2007-03-08')
(18, 'Nicole', 13.44, '2007-03-08')
(19, 'Britney', 8.78, '2007-03-08')
(20, 'Ashley', 26.82, '2007-03-08')
(21, 'Lindsey', 3.71, '2007-03-07')
(22, 'Nicole', 0.56, '2007-03-07')
(23, 'Britney', 34.19, '2007-03-07')
(24, 'Ashley', 7.77, '2007-03-07')
(25, 'Lindsey', 16.23, '2007-03-06')
(26, 'Nicole', 0, '2007-03-06')
(27, 'Britney', 4.5, '2007-03-06')
(28, 'Ashley', 19.22, '2007-03-06')
SUM(sales)
('Britney', 107.91)
('Nicole', 98.23)
('Ashley', 96.02999999999999)
('Lindsey', 81.08000000000001)
AVG(sales)
('Britney', 15.415714285714285)
('Nicole', 14.032857142857143)
('Ashley', 13.718571428571426)
('Lindsey', 11.582857142857145)
MIN(sales)
('Ashley', 0)
('Lindsey', 0)
('Nicole', 0)
('Britney', 2.58)
MAX(sales
('Britney', 43.21)
('Lindsey', 32.02)
('Nicole', 31.99)
('Ashley', 26.82)
$