*ブログに戻る →ここ です。
GROUP BY句
カラムの値が重複しているものをグループとして平均を求める 株価の平均に使えるね free92
Friday,August,2,2013
sada_d sada_name sada_ price sada_category
20130802 Toyota 6220 Car *グループt(sada_name)
20130802 Mazda 400 Car *グループm(sada_name)
20130802 Honda 3725 Car *グループh(sada_name)
20130802 Suzuki 2400 Car 以下下につづく
20130802 Mitsubishi motors 1300 Car ↓
20130802 Daihatsu 2200 Car ↓
20130802 Sony 2000 Home electronics ↓
20130802 Panasonic 900 Home electronics ↓
20130802 Sharp 400 Home electronics
20130802 Dena 2000 It
20130802 Rakuten 1300 It
20130802 Gungho 100000 It
20130105 Toyota 4200 Car *グループt(sada_name)
20130105 Mazda 180 Car *グループm(sada_name)
20130105 Honda 3400 Car *グループh(sada_name)
20130105 Suzuki 2400 Car
20130105 Mitsubishi motors 850 Car
20130105 Daihatsu 1750 Car
20130105 Sony 1000 Home electronics
20130105 Panasonic 550 Home electronics
20130105 Sharp 300 Home electronics
20130105 Dena 3000 It
20130105 Rakuten 700 It
20130105 Gungho 10000
20120601 Toyota 3000 Car *グループt(sada_name)
20120601 Mazda 100 Car *グループm(sada_name)
20120601 Honda 2500 Car *グループh(sada_name)
20120601 Suzuki 1600 Car
20120601 Mitsubishi motors 800 Car
20120601 Daihatsu 1300 Car
20120601 Sony 1000 Home electronics
20120601 Panasonic 550 Home electronics
20120601 Sharp 400 Home electronics
20120601 Dena 1500 It
20120601 Rakuten 800 It
20120601 Gungho 0 It
○テーブル設定メモ
VARCHAR型なら、unique その他のオプション default 'データなし', default 0, not null(データなし を許さない)
INT型なら、primary key
○テーブルを作る ↓db名 ↓テーブル名 ↓カラム名 ↓カラム名 ↓型(文字を格納)
mysql> create table sadachika_shop.sadachi_marketsjp(sada_d date, sada_name varchar(20) default '銘柄データなし', sada_price int,
-> sada_category varchar(20) default 'カテデータなし'); ↑型(日付を格納)
Query OK, 0 rows affected (0.09 sec)
○テーブルの削除場合はこんな感じ
mysql> drop table sadachika_shop.sadachi_marketsjp; *テーブルを作る際に間違えたらカラム追加や訂正より削除のほうが簡単
Query OK, 0 rows affected (0.22 sec)
○カラムの構造を見る
mysql> show columns from sadachika_shop.sadachi_marketsjp;
Field Type Null Key Default Extra
sada_d date YES NULL
sada_name varchar(10) YES 銘柄データなし
sada_price int(11) YES NULL
sada_category varchar(10) YES カテデータなし
4 rows in set (0.02 sec)
○値を入力する
mysql> insert into sadachika_shop.sadachi_marketsjp values
->(20130802,'Toyota',6220,'Car'),(20130802,'Mazda',400,'Car'),(20130802,'Honda',3725,'Car'),(20130802,'Suzuki',2400,'Car'),
->(20130802,'Mitsubishi motors',1300,'Car'),(20130802,'Daihatsu',2200,'Car'),(20130802,'Sony',2000,'Home electronics'),
->(20130802,'Panasonic',900,'Home electronics'),(20130802,'Sharp',400,'Home electronics'),(20130802,'Dena',2000,'It'),
->(20130802,'Rakuten',1300,'It'),(20130802,'Gungho',100000,'It');
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> insert into sadachika_shop.sadachi_marketsjp values
->(20130105,'Toyota',4200,'Car'),(20130105,'Mazda',180,'Car'),(20130105,'Honda',3400,'Car'),(20130105,'Suzuki',2400,'Car'),
->(20130105,'Mitsubishi motors',850,'Car'),(20130105,'Daihatsu',1750,'Car'),(20130105,'Sony',1000,'Home electronics'),
->(20130105,'Panasonic',550,'Home electronics'),(20130105,'Sharp',300,'Home electronics'),(20130105,'Dena',3000,'It'),
->(20130105,'Rakuten',700,'It'),(20130105,'Gungho',10000,'It');
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> insert into sadachika_shop.sadachi_marketsjp values
->(20120601,'Toyota',3000,'Car'),(20120601,'Mazda',100,'Car'),(20120601,'Honda',2500,'Car'),(20120601,'Suzuki',1600,'Car'),
->(20120601,'Mitsubishi motors',800,'Car'),(20120601,'Daihatsu',1300,'Car'),(20120601,'Sony',1000,'Home electronics'),
->(20120601,'Panasonic',550,'Home electronics'),(20120601,'Sharp',400,'Home electronics'),(20120601,'Dena',1500,'It'),
->(20120601,'Rakuten',800,'It'),(20120601,'Gungho',0,'It');
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
○テーブルの中身の表示
mysql> select * from sadachika_shop.sadachi_marketsjp;
sada_d sada_name sada_price sada_category
2013-08-02 Toyota 6220 Car *グループt(sada_name)20130802
2013-08-02 Mazda 400 Car *グループm(sada_name)20130802
2013-08-02 Honda 3725 Car *グループh(sada_name)20130802
2013-08-02 Suzuki 2400 Car 以下下につづく
2013-08-02 Mitsubishi motors 1300 Car ↓
2013-08-02 Daihatsu 2200 Car ↓
2013-08-02 Sony 2000 Home electronics ↓
2013-08-02 Panasonic 900 Home electronics ↓
2013-08-02 Sharp 400 Home electronics
2013-08-02 Dena 2000 It
2013-08-02 Rakuten 1300 It
2013-08-02 Gungho 100000 It
2013-01-05 Toyota 4200 Car *グループt(sada_name)20130105
2013-01-05 Mazda 180 Car *グループm(sada_name)20130105
2013-01-05 Honda 3400 Car
2013-01-05 Suzuki 2400 Car
2013-01-05 Mitsubishi motors 850 Car
2013-01-05 Daihatsu 1750 Car
2013-01-05 Sony 1000 Home electronics
2013-01-05 Panasonic 550 Home electronics
2013-01-05 Sharp 300 Home electronics
2013-01-05 Dena 3000 It
2013-01-05 Rakuten 700 It
2013-01-05 Gungho 10000 It
2012-06-01 Toyota 3000 Car *グループt(sada_name)20120601
2012-06-01 Mazda 100 Car *グループm(sada_name)20120601
2012-06-01 Honda 2500 Car
2012-06-01 Suzuki 1600 Car
2012-06-01 Mitsubishi motors 800 Car
2012-06-01 Daihatsu 1300 Car
2012-06-01 Sony 1000 Home electronics
2012-06-01 Panasonic 550 Home electronics
2012-06-01 Sharp 400 Home electronics
2012-06-01 Dena 1500 It
2012-06-01 Rakuten 800 It
2012-06-01 Gungho 0 It
○○社名をグループで縛りし、株価の平均を求める ↓重複の値をグループにする(20130802Toyotaと20130105Toyotaと20120601Toyotaは同じ名前であるのでグループ)
mysql> select sada_name, AVG(sada_price) FROM sadachika_shop.sadachi_marketsjp GROUP BY sada_name DESC;
↑カラム名 ↑平均 ↑カラム名 ↑データベース名 ↑テーブル名 ↑カラム名 ↑降順 *ASC昇順
sada_name AVG(sada_price)
Toyota 4473.3333(6220+4200+3000)/3=4473.3333
Suzuki 2133.3333(2400+2400+1600)/3=2133.3333
Sony 1333.3333(2000+1000+1000)/3=1333.3333
Sharp 366.6667
Rakuten 933.3333
Panasonic 666.6669
Mitsubishi motors 983.3333
Mazda 226.6667(400+180+100)/3=226.6666
Honda 3208.3333
Gungho 36666.6667(100000+10000+0)/3=36666.6666
Dena 2166.6667
Daihatsu 1750.0000(2200+1750+1300)/3=1750
アルファベット順
1,daihatsu
2,Dena
3,Gungho
4,honda
5,mazda
6,mitsubishi-motors
7,panasonic
8,rakuten
9,Sharp
10,sony
11,suzuki
12,toyota
* 参考にしたサイトは→ こちら です。
これは赤色の文字例です。
これは青色の文字例です。