営業事務(マーケ担当)はSQLを覚えるべきだし、2時間で覚えられる。
めっちゃええ話。これはすごく意味がある。細かいツッコミをすれば、上記はSQLを簡単に投げられるようになった話でありSQLを駆使できるようになったわけではないのだけど、そのうち覚えるやろ。
弊社では商品企画を担当する社員がおりまして、ExcelでVLOOKUPはできるけどデータベースの操作はしたことがない、というレベルです。僕が隣でSQLを書いているのを見て、「これなら出来そうだから教えて」と言われたので、教えました。2時間の講習でここまで覚えてくれました。
- データベースとテーブルの関係
- SELECT,FROM,WHERE,LIKE,ORDER BY
- JOIN,GROUP BY,CASE-WHEN
- 簡単なDB関数
なので、「エプロンという品名の商材を抽出して、伝票日付が4月と5月の売上の2013年以降の年度別の対比が見たい」というSQLが書けるようになりました。具体的にはこんな感じです。
select year(e.billdate) as yearly, sum(case when month(e.billdate) = 4 then ed.ordernum else 0 end) as 4月, sum(case when month(e.billdate) = 5 then ed.ordernum else 0 end) as 5月 from earning_details as ed join items as i on i.id = ed.item_id join earnings as e on e.id = ed.earning_id where e.billdate >= 2013 and i.name like '%エプロン%' group by yearly
微妙に構文が違ったりすることはあるんですが、その辺はどうとでもなります。やってる内容がわかれば。
以下、説明に使ったナイストーク集です。
データベース is 何
Excelで例えました。データベースはブック、テーブルはシート。シートに名前があるように、テーブルにも名前がある。また、シートに作れるのは一覧表だけ。一番上の行にカラムの名前を決める。商品というシートならそこに品番、品名、売値などがあって、それと同じという話をした。3分で理解できました。
JOIN is 何
JOINは別テーブルにある同じカラムで参照することで表を連結することでーすと説明しました。売上明細には品番はないけど、商品IDというカラムがあります。売上明細の商品IDカラムは商品テーブルのIDと同じものですので、商品IDでくっつけると横になが〜い表ができるでしょという話をしました。3分で理解できました。
こまけぇこたぁいいんだ。SQLは集合指向であることさえ伝われば。
改めて思ったのは、SQLはSELECTが9割ってことでした。おしまい。
2020.01.06 追記
SQLを学習できるWebサービス作っちゃいました。
www.start-sql.net
プログラミング未経験者がSQLの習得に挑戦するでござる
select itemcode, name, lot, price from items where itemcode like '%MA-080%' order by itemcode asc
元々はこんなSQLを書いているのを実妹が見ていて「これなら私でも出来そうだから教えて」と話が来た所に端を発する。
確かに言われてみれば、下記のPHPのコード(WPのfuncitions.php)よりも圧倒的に文脈がわかりやすいわ。
<?php function custom_login_logo_url_title() { return 'Custom,Inc.'; } add_filter( 'login_headertitle', 'custom_login_logo_url_title' ); function custom_login_logo_url() { return get_bloginfo( 'url' ); } add_filter( 'login_headerurl', 'custom_login_logo_url' );
データベースとのテーブルの関係なんて、Excelに例えたらブックとシートみたいなもんだ。「=Sheet2!B1」でテーブル名Sheet2の左から2番めのカラムをさしまーすとか、適当に説明が付くし。
1週間あればクロス集計ぐらいできるようになるんちゃうかな〜 先生がええからな〜(え
WordPressのwp_cronの実行スケジュール雑過ぎワロタ
WordPressにwp_cronとかいう関数があるんで、おおデーモンになってくれて空気読んでくれるのかなとおもいきや、そんな訳がなかった。
処理がスケジューリングされていても、サイトにアクセスがなければ実行はされませんし、アクセスのあった時間が基準となるため、確実に指定時間に実行されるわけではありません。
WordPressで擬似cronを使ってバッチ処理を定期実行させる方法 | コワーキングスペース7F
なんですと。Webアクセスをきっかけにcronの実行可否を判断するわけだ。9時半にセットしても9時45分にアクセスが来たら、実行時刻は9時45分になるってことだね。なるほどね〜
ただ「定期的にWebアクセスをすればスケジュール通りに実行される」のであれば、一番簡単のはcurlするcronを仕込めばそれで終わりか。そう考えると「まぁそれもありか」という気がする。
UNION句を久しぶりに使ったのでメモ
テストデータを整備するのに、YというテーブルのIDを外部キーとして持っているA〜Dのテーブルのレコードを集めて1つの表にする必要がありました。
こういう時はUNIONの出番です。
select zzz.id, zzz.name from ( selct y.id, y.name, from y_table as y join a_table as a on a.y_id = y.id --A_table union selct y.id, y.name, from y_table as y join b_table as b on b.y_id = y.id --B_table union selct y.id, y.name, from y_table as y join c_table as c on c.y_id = y.id --C_table union selct y.id, y.name, from y_table as y join b_table as d on d.y_id = y.id --D_table ) as zzz
派生テーブルにしておけばorder_byもwhere句も使えます。zzzの後に。
ただ、MYSQLの場合はUNIONした結果をFROM句に持ってくるのはサブクエリでしか無いので、WHEREをFROM句の外に書いてしまうと爆裂重くなるかもしれません。その時はUNIONしたい集合をはじめからWHEREで絞りましょう。
SQLのFROM句はテーブルである必要はございません!
どうもそのように思っている方が多い気がしたので。
FROM句に指定できるのはテーブルそのものではなく、SELECT文を発行した結果でも指定できます。派生テーブルという表現をすることが多いみたい。
PostgreSQLのマニュアルでもそんなような事が書いてあります。
FROM句は、カンマで分けられたテーブル参照リストで与えられる1つ以上のテーブルから、1つのテーブルを派生します。
テーブル参照は、テーブル名(スキーマで修飾することもできます)、副問い合わせによる派生テーブル、結合の組み立て、またはこれらの複雑な組み合わせからなります。
https://www.postgresql.jp/document/9.5/html/queries-table-expressions.html#queries-from
派生テーブルの例
こんなやつ。from句でテーブルではなくSQLの選択結果をFROMに入れています。
select * from ( select id, user_id, access_path, created from logs where year(created) = 2015 order by id desc ) as tmp_table group by user_id
オヌヌメ本
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る
Pythonでdictを比較してassertしたい
単体テストのコードを書いていて、そういう局面にぶち当たりました。
結論から言うとこれでOKです。
assert all( (k,v) in source_dict.items() for (k,v) in dest_dict.items() )
こちらを参考にしました。
SQLAlchemyでDB固有の関数を実行する
難しいことはありませんでした。
以下はMySQLのtruncateという切り捨ての関数を使う例です。
rs = Item.query.\ filter(Item.maker_id == param['maker_id']).\ filter(func.truncate(Item.gedai / Item.price, 1) < 0.6).all()
SQLAlchemyのfuncオブジェクトは空気が読めるナイスガイなので、funcの後にDB関数名をそのまま書いて実行することが出来ます。
上記の例ですと、「truncate(items.gedai / items.price, %s) < %s」というSQLに変換されて実行してくれます。
また、SQLAlchemyのORMapperが生成されたSQLを確認するには、下記のように行います。Queryオブジェクトを引数に取ります。
def print_sql(query): ''' debug用途でSQL文を吐き出す ''' from sqlalchemy.dialects import mysql print(query.statement.compile(dialect=mysql.dialect()))
I ♥ SQLAlchemy!! Yummy!!