SQL 集約関数とGROUPBY

INSERTサンプル

BEGIN TRANSACTION;

INSERT INTO soft VALUES ('1', 'eclipse', '100');
INSERT INTO soft VALUES ('2', 'vscode', '200');
INSERT INTO soft VALUES ('3', 'visual studio', '300');
INSERT INTO soft VALUES ('4', 'Atom', '400');
INSERT INTO soft VALUES ('5', 'pycharm', '500');
INSERT INTO soft VALUES ('6', 'sublime text', '600');
INSERT INTO soft VALUES ('7', 'DBeaver', '700');
 
COMMIT;

SELECT

基本形

# 全て
select * from soft;

# 列を指定 ※idとpriceが列名
select id, price from soft;

ASで列に別名をつける

# idにsoft_idという名前を付けている
# 日本語を使用する場合は、""(ダブルクオーテーション)で囲む。
select id as soft_id, price as "値段" from soft;

# 定数の出力なども可能
select 'おまけ' as omake,  id as soft_id from soft;

値の重複を省く

# 出力結果で重複が省かれる
select distinct name from soft;

条件式

# idを1に絞って出力
select * from soft where id = '1';

# NULLはis null となる
select price from soft where price is null;

算術と比較演算子

# price を300以上に絞って出力
select * from soft where price > 300;

# price を2倍で出力
select id, price * 2 as price_x2 from soft;

# 否定は<>を使う
# 300以外を取得
select *  from soft where price <> 300;

論理演算子

# NOT演算子 
# priceが400以上ではないという条件
select *  from soft where not price  > 400;

# AND
select * from soft where price > 300 and id <> '7';

# OR 
select * from soft where price > 300 or id = '1';

テーブルを集約

# COUNT 行数を数える
select count(*) from soft;
# NULLを除外するには、列を指定
select count(price) from soft;


# SUM 合計を取得
# priceの合計を取得する
select sum(price) from soft;


# AVG 平均値を取得
SELECT AVG (price) from soft;

# MAX 最大値を取得
# MIN 最小値を取得
SELECT MAX(price) , MIN (price) from soft;

# 重複値を省きつつ、集約する
select max(distinct price ) from soft;

説明用にカラムを追加

# 値は適当です。
alter table soft add column category char(10);

BEGIN TRANSACTION;

update soft set category = 'IDE' where id = '1';
update soft set category = 'Editor' where id = '2';
update soft set category = 'IDE' where id = '3';
update soft set category = 'Editor' where id = '4';
update soft set category = 'IDE' where id = '5';
update soft set category = 'Editor' where id = '6';
update soft set category = 'IDE' where id = '7';

COMMIT;

グループに分ける

# GROUP BY 
# 値が等しい行をグループ分けすることができる。
# この例では、categoryのIDEとEditorをグループ分けして、priceの最大値を取得している。
select category, MAX(price) from soft group by category;

# whereで条件式を使う場合は、条件で一致した値に対してGROUP BYが実行される。
select category, MIN(price) from soft where price >= 200 group by category;

HAVING

GROUP BYでグループ化した結果に条件を指定することができる。

# group byの後に記述する。
# categoryでgroup by したpriceに最大値が600で絞る
select category, MAX(price) from soft group by category having MAX(price) > 600;

並べ替え

order by

# asc 昇順 
#何も付けないと昇順
select * from soft order by price;

# desc 降順
select * from soft order by price desc;


#集約関数も使用可能
select category, MAX(price) from soft group by category order by MAX(price) desc;