SQL Server CHECKSUM_AGG チェックサム

CHECKSUM_AGG チェックサム

グループ内にある値のチェックサムが欲しい場合、CHECKSUM_AGG関数を使います。
どのようなケースでチェック
NULL 値は無視されます。

構文:CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT CHECKSUM_AGG で、一意な値のチェックサムを返します。
   expression 整数式です。 集計関数とサブクエリは使用できません。

CHECKSUM_AGG は、テーブル内の変更を検出する場合に使用できます。
テーブル内での行の順序は、CHECKSUM_AGG の結果に影響しません。

対象データを変更した場合は、そのリストのチェックサムも変わりますが、 計算の結果チェックサムが変わらない場合もあります。

CHECKSUM_AGG の AGG は、aggregate (集計)の意味です。

下のSQL例文を載せておきます。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (4)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (NULL)
insert into @tbl1 (c_point) values (80)

--ALL
--NULLはカウントされません
select CHECKSUM_AGG(c_point) from @tbl1

--DISTINCT
--60が重複しているので結果が異なります
select CHECKSUM_AGG(DISTINCT c_point) from @tbl1

--値が変更されると戻り値も変わります
update @tbl1 set c_point=70 where c_point=80
select CHECKSUM_AGG(c_point) from @tbl1

計算の結果チェックサムが変わらない例
下の例は非常に単純なので実際はこんなに簡単にチェックサムが変わらないことは無いと思いますが。
30 → 31 , 40 → 41 で同じ値を取ります。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)

--ALL
select CHECKSUM_AGG(c_point) from @tbl1

--値が変更されると戻り値も変わります
update @tbl1 set c_point=31 where c_point=30
update @tbl1 set c_point=41 where c_point=40
select CHECKSUM_AGG(c_point) from @tbl1

以上、SQL Server でチェックサムが欲しい場合、CHECKSUM_AGG関数を使い求めるでした。

SQL Server COUNT カウント 数える

COUNT カウント

SQL Server の対象データの行数を(アイテム数)求めるときに、COUNT関数もしくはCOUNT_BIG関数を使います。
COUNT は常に int データ型の値を返します。
COUNT_BIG は常に bigint データ型の値を返します。

構文:COUNT ( [ ALL | DISTINCT ] expression | * )
    OVER ( [ partition_by_clause ] order_by_clause )
引数:ALL すべての値に集計関数が適用されます。 ALL が既定値です。
   DISTINCT COUNT で、NULL でない一意な値の数を返します。
   expression text 、image、ntext 以外のあらゆる型の式です。
* すべての行を数えて、テーブル内の行の総数を返すことを指定します。
COUNT(*) はパラメーターはとらず、DISTINCT と一緒には使用できません。
COUNT(*) は重複値を除去しないで、指定されたテーブル内の行数を返します。
各行は 1 行としてカウントされ、 これには NULL 値を保持している行も含まれます。
OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause は、FROM 句で生成された結果セットをパーティションに分割します。

下のSQL例文を載せておきます。

declare @tbl1 table(a_goods varchar(3), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','7',NULL)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','8',220)

--ALL
--NULLはカウントされません
select COUNT(ALL c_point) from @tbl1
select COUNT(c_point) from @tbl1 --ALLが無くても同じ結果

--COUNT_BIG
select COUNT_BIG(ALL c_point) from @tbl1

--DISTINCT
--60が重複しているので1つ減ります
select COUNT(DISTINCT c_point) from @tbl1

--*
--行がカウントされます
select COUNT(*) from @tbl1

--over
--a_goods毎にc_pointがカウントされます
select distinct a_goods, COUNT(c_point) OVER (PARTITION BY a_goods) from @tbl1

もう一例
AVG や SUM を共に使用して結果を求める

declare @tbl1 table(a_goods varchar(5), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',20)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',20)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','3',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',40)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','1',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','2',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','1',50)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','1',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',10)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',30)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','2',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','3',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',20)

--商品、社員ごとのデータ
select distinct a_goods, b_empNo
, COUNT(c_point) over (partition by a_goods,b_empNo) 個数
, SUM(c_point) over (partition by a_goods,b_empNo) 合計
, AVG(c_point) over (partition by a_goods,b_empNo) 平均 
from @tbl1

--社員ごとのデータ
select distinct b_empNo
, COUNT(c_point) over (partition by b_empNo) 個数
, SUM(c_point) over (partition by b_empNo) 合計
, AVG(c_point) over (partition by b_empNo) 平均 
from @tbl1


--商品、社員ごとのデータ
select a_goods, b_empNo
, COUNT(c_point) 個数
, SUM(c_point) 合計
, AVG(c_point) 平均
from @tbl1 group by a_goods, b_empNo order by a_goods, b_empNo

--社員ごとのデータ
select b_empNo
, COUNT(c_point) 個数
, SUM(c_point) 合計
, AVG(c_point) 平均
from @tbl1 group by b_empNo order by b_empNo

以上、SQL Server で COUNT関数を使いアイテム数や行数を求めるでした。

SQL Server MAX 最大

MAX 最大

SQL Server の対象データの最大値を求めるときに、MAX関数を使います。
数値型、日付型、文字列の最大値を求める事ができます。

構文:MAX ( [ ALL | DISTINCT ] expression )
引数:ALL すべての値に集計関数が適用されます。 ALL が既定値です。
   DISTINCT MAX では意味がなく、ISO との互換性を保つためだけに指定可能になっています。
   expression 定数、列、関数、および算術演算子、ビット演算子、文字列演算子の組み合わせを指定します。

下のSQL例文を載せておきます。

declare @tbl1 table(a_goods varchar(3), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',800000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',400000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',300000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',500000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',2200000)

--ALL
select MAX(ALL c_point) from @tbl1
select MAX(c_point) from @tbl1 --ALLが無くても同じ結果

--DISTINCT
--もともと一番大きい値を返すので意味がない
select MAX(DISTINCT c_point) from @tbl1

もう一例
over や group by 句で括って結果を求める

declare @tbl1 table(a_goods varchar(5), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',20)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',20)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','3',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','4',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','5',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','7',40)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','1',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','4',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','5',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','7',50)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','1',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','4',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','5',10)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','6',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','7',30)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','4',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','5',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','7',20)

select MAX(c_point) from @tbl1
select MAX(c_point) from @tbl1 where a_goods='001'
select MAX(c_point) from @tbl1 where a_goods='002'
select MAX(c_point) from @tbl1 where a_goods='003'
select MAX(c_point) from @tbl1 where a_goods='004'
select MAX(c_point) from @tbl1 where a_goods='005'
select a_goods, MAX(c_point) from @tbl1 group by a_goods  --商品の最大値

select distinct b_empNo, MAX(c_point) over (partition by b_empNo) from @tbl1  --社員の最大値 
select b_empNo, MAX(c_point) from @tbl1 group by b_empNo  --社員の最大値

以上、SQL Server で MAX関数を使い最大値を求めるでした。

SQL Server MIN 最小

MIN 最小値

SQL Server の対象データの最小値を求めるときに、MIN関数を使います。
数値型、日付型、文字列の最小値を求める事ができます。

構文:MIN ( [ ALL | DISTINCT ] expression )
引数:ALL すべての値に集計関数が適用されます。 ALL が既定値です。
   DISTINCT MIN では意味がなく、ISO との互換性を保つためだけに指定可能になっています。
   expression 定数、列、関数、および算術演算子、ビット演算子、文字列演算子の組み合わせを指定します。

下のSQL例文を載せておきます。

declare @tbl1 table(a_goods varchar(3), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',800000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',400000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',300000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',500000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',2200000)

--ALL
select MIN(ALL c_point) from @tbl1
select MIN(c_point) from @tbl1 --ALLが無くても同じ結果

--DISTINCT
--もともと一番小さい値を返すので意味がない
select MIN(DISTINCT c_point) from @tbl1

もう一例
over や group by 句で括って結果を求める

declare @tbl1 table(a_goods varchar(5), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',20)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',20)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','3',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','4',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','5',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','7',40)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','1',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','4',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','5',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','7',50)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','1',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','4',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','5',10)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','6',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','7',30)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','4',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','5',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','7',20)

select MIN(c_point) from @tbl1
select MIN(c_point) from @tbl1 where a_goods='001'
select MIN(c_point) from @tbl1 where a_goods='002'
select MIN(c_point) from @tbl1 where a_goods='003'
select MIN(c_point) from @tbl1 where a_goods='004'
select MIN(c_point) from @tbl1 where a_goods='005'
select a_goods, MIN(c_point) from @tbl1 group by a_goods  --商品の最小値

select distinct b_empNo, MIN(c_point) over (partition by b_empNo) from @tbl1  --社員の最小値 
select b_empNo, MIN(c_point) from @tbl1 group by b_empNo  --社員の最小値

以上、SQL Server で MIN関数を使い最小値を求めるでした。

SQL Server SUM 合計

SUM 合計

SQL Server の対象データの合計値の求めるときに、SUM関数を使います。

合計値はいろいろなデータ分析で使用されるので良く使う指標だと思います。

構文:SUM ( [ ALL | DISTINCT ] expression )
引数:ALL すべての値に集計関数が適用されます。 ALL が既定値です。
   DISTINCT 値の出現回数にかかわらず、一意な値の合計を返すことを指定します。
   expression 定数、列、関数、および算術演算子、ビット演算子、文字列演算子の組み合わせを指定します。
NULL 値はすべて無視されます。(対象データになりません)

下のSQL例文を載せておきます。

declare @tbl1 table(a_goods varchar(3), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',800000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',400000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',300000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',500000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',600000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',2200000)

--ALL
select SUM(ALL c_point) from @tbl1
select SUM(c_point) from @tbl1 --ALLが無くても同じ結果

--DISTINCT
--600000が2行あるため、重複を省き計算される
select SUM(DISTINCT c_point) from @tbl1

--expression
--型変換、桁あふれに対応する
delete from @tbl1
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',800000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',600000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',400000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',300000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',500000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',600000000)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',1100000000)
select SUM(cast(c_point as bigint)) from @tbl1  --bigint型に
select SUM(cast(c_point as decimal(20,2))) from @tbl1  --decimal型に

もう一例
over や group by 句で括って結果を求める

declare @tbl1 table(a_goods varchar(5), b_empNo char(1), c_point int)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','1',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','4',20)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','5',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('001','7',20)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','1',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','3',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','4',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','5',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('002','7',40)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','1',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','2',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','4',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','5',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','6',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('003','7',50)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','1',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','2',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','3',90)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','4',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','5',70)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','6',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('004','7',30)

insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','1',80)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','2',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','3',40)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','4',30)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','5',50)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','6',60)
insert into @tbl1 (a_goods,b_empNo,c_point) values ('005','7',20)

select SUM(c_point) from @tbl1
select SUM(c_point) from @tbl1 where a_goods='001'
select SUM(c_point) from @tbl1 where a_goods='002'
select SUM(c_point) from @tbl1 where a_goods='003'
select SUM(c_point) from @tbl1 where a_goods='004'
select SUM(c_point) from @tbl1 where a_goods='005'
select a_goods, SUM(c_point) from @tbl1 group by a_goods  --商品ごとの平均点

select distinct b_empNo, SUM(c_point) over (partition by b_empNo) from @tbl1  --社員ごとの平均点 
select b_empNo, SUM(c_point) from @tbl1 group by b_empNo  --社員ごとの平均点

以上、SQL Server で SUM関数を使い合計値を求めるでした。

SQL Server AVG 平均

AVG 平均

SQL Server の対象データの平均値の求めるときに、AVG関数を使います。

平均値はいろいろなデータ分析で使用されるので良く使う指標だと思います。

構文: AVG ( [ ALL | DISTINCT ] expression )
引数: ALL すべての値に集計関数が適用されます。 ALL が既定値です。
    DISTINCT 一意な値の合計を返すことを指定します。
expression 定数、列、関数、および算術演算子、ビット演算子、文字列演算子の組み合わせを指定します。
NULL 値はすべて無視されます。(対象データになりません)

下のSQL例文を載せておきます。

declare @tbl1 table(a_class varchar(3), b_kbn char(1), c_point int)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','1',80)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','2',60)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','3',40)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','4',30)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','5',50)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','6',60)
insert into @tbl1 (a_class,b_kbn,c_point) values ('001','7',22)

--ALL
select AVG(ALL c_point) from @tbl1
select AVG(c_point) from @tbl1 --ALLが無くても同じ結果

--DISTINCT
--60が2行あるため、重複を省き計算される
select AVG(DISTINCT c_point) from @tbl1

--expression
--型変換、小数点以下も求める
select AVG(cast(c_point as float)) from @tbl1 --float型に
select AVG(cast(c_point as decimal(5,2))) from @tbl1
select cast(AVG(cast(c_point as decimal(5,2))) as decimal(5,2)) from @tbl1

もう一例
over や group by 句で括って結果を求める

declare @tbl1 table(a_class varchar(5), b_stdNo char(1), c_point int)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','1',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','2',70)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','3',40)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','4',20)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','5',40)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','6',50)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('国語','7',20)

insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','1',70)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','2',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','3',50)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','4',80)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','5',30)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','6',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('算数','7',40)

insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','1',90)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','2',70)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','3',40)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','4',70)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','5',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','6',50)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('理科','7',50)

insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','1',30)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','2',50)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','3',90)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','4',40)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','5',70)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','6',80)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('社会','7',30)

insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','1',80)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','2',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','3',40)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','4',30)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','5',50)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','6',60)
insert into @tbl1 (a_class,b_stdNo,c_point) values ('英語','7',20)

select avg(c_point) from @tbl1
select avg(c_point) from @tbl1 where a_class='国語'
select avg(c_point) from @tbl1 where a_class='算数'
select avg(c_point) from @tbl1 where a_class='理科'
select avg(c_point) from @tbl1 where a_class='社会'
select avg(c_point) from @tbl1 where a_class='英語'
select a_class, avg(c_point) from @tbl1 group by a_class  --科目ごとの平均点

select distinct b_stdNo, AVG(c_point) over (partition by b_stdNo) from @tbl1  --生徒ごとの平均点 
select b_stdNo, AVG(c_point) from @tbl1 group by b_stdNo  --生徒ごとの平均点

以上、SQL Server で AVG関数を使い平均値を求めるでした。

SQL Server トランザクション

トランザクション

トランザクション(transaction)とは、不整合を起こさせないための一連の操作の集合のことで、一連の操作全体が一つの単位とみなされます。
複数のSQLを発行してデータ操作をしても、トランザクションを正常に終了させないと、全くデータ操作をしなかったのと同じになるようにされています。

新幹線のチケット販売を考えたとき、支払と切符の販売はセットであり、片方だけでは不都合が起こることから、支払と販売はトランザクション管理されないといけないことが容易に想像できると思います。

トランザクションの開始

SQL Server でトランザクション処理を開始するには、’bigin tran’句を発行します。
トランザクションは’bigin tran tran名’と言うようにトランザクション名を付けることができます。
トランザクション名は 32 文字まで有効です。(超えた分は切り捨てられます)

SQL Server は分散処理もできるため、トランザクションを分散トランザクションとしても実行できます。
その他、マークを付けたりもできます。
また、おかしなことを言うようですが、ネストもできます。複雑なデータ操作だと必要になることあるそうです。
高度なトランザクション管理を行いたい場合は、より深く調査をしてください。

一般的な一連の操作で済む処置であれば(ほとんどがそうなると思うのですが。。。)、以下のように発行してください。


BEGIN TRAN T1

トランザクション終了

トランザクションを終了させるには、’commit’句を発行します。


COMMIT TRAN T1

トランザクションを中断する

何らかのトラブルがあり、トランザクションを中断させ、データ操作を無かったことにしたい場合に’rollback’句を発行します。
プログラムの中だとエラーハンドリンして、その中でロールバックさせるのが一般的です。


rollback

以下に簡単なトランザクションの例を載せておきます。

正常に終了するパターン:

create table tbl1 (a_cd nvarchar(9))
create table tbl2 (a_cd nvarchar(9))

declare @acd nvarchar(9)
set @acd='00001'

begin tran t1

begin try

  insert into tbl1 (a_cd) values (@acd)
  insert into tbl2 (a_cd) values (@acd)

  commit tran t1

end try
begin catch

  rollback

end catch

select * from tbl1
select * from tbl2

-----後処理
drop table tbl1
drop table tbl2

set @acd=1/0を入れエラーを起こさせたパターン:

create table tbl1 (a_cd nvarchar(9))
create table tbl2 (a_cd nvarchar(9))

declare @acd nvarchar(9)
set @acd='00001'

begin tran t1

begin try

  insert into tbl1 (a_cd) values (@acd)
  insert into tbl2 (a_cd) values (@acd)
  set @acd=1/0

  commit tran t1

end try
begin catch

  rollback

end catch

select * from tbl1
select * from tbl2

-----後処理
drop table tbl1
drop table tbl2

動かしてみるとわかりますが、rollbackも含め正常にトランザクション処理されています。

なお、テーブル変数を使うと、テーブル変数は持続性のあるデータベースの一部ではないため、トランザクションのロールバックによる影響を受けません。
ロールバックしてもデータは途中までの操作が有効になったままなので、トランザクション処理を行う時は要注意です。

以上、トランザクションの簡単な説明でした。

SQL Server リテラル

リテラル

SQL Serverにおいてリテラルとは、識別子によって表現する必要がない明示的な数値、テキスト、日時などのことです。
たとえば、数値リテラルは、’100’や’1000’などの数を示す値です。
テキスト(文字列)リテラル ‘SQL Server’ など、日時リテラルは’2017/12/15’などです。

数値リテラル

SQL Server では取り扱える型によって少しづつ指定が違います。
引用符では囲みません。

binary 型定数は 16 進数の文字列であり、0x というプレフィックスが付きます。
bit 型定数は数値の 0 または 1 で表します。
integer 型定数は数値文字列で表し、小数点を含まない整数である必要があります。
decimal 型定数は、小数点を含む数値文字列で表せます。
float 型定数と real 型定数は科学的表記法で表します。(101.5E5, 0.5E-2)
money 型定数は数値文字列で表し、オプションで小数点および通貨記号をプレフィックスとして付加することができます。

また、数値が正であるか負であるかを示すには、数値型定数に + または – 単項演算子を付加します。

テキスト(文字列)リテラル

英数字 (a ~ z、A ~ Z、および 0 ~ 9)、感嘆符 (!)、アット マーク (@)、および番号記号 (#) などの特殊文字を単一引用符で囲みます。
単一引用符で囲まれた文字列に単一引用符を埋め込む場合は、単一引用符を 2 つ続けて並べることで 1 つの単一引用符を表します。 文字列が二重引用符で囲まれている場合は該当しません。

文字列リテラルに N プレフィックスを付けると Unicode 文字列になります。

実はこのテキストリテラル、とても奥深いです。
私も深く掘り下げると理解できないところが出てきます。
UNICODEとか、照合順位とかいろいろありますので、興味がでたら調べてみてください。

日時リテラル

datetime 型を利用します
単一引用符で囲みます。

‘December 5, 1985’
‘5 December, 1985’
‘851205’
’12/5/98′
など、さまざま表記法があります。

以上、リテラルについての簡単な説明でした。

SQL Server INTERSECT

INTERSECT

INTERSECT は、ユニオンクエリと呼ばれ、1つのクエリ結果と別のクリエ結果の同じ行のみの結果を返します。
ユニオンクエリには、UNION, EXCEPT 句もあります。

ちなみに、SQL Server には、INTERSECT ALL はありません。

INTERSECT の使い方

1つの目のクエリ結果と、次のクリエ結果を比較し、同じ行(レコード)のみを結果として返します。

以下に簡単な例を書きます。
tble1の結果からtbl2と同じ結果(行)があるもののみ結果を返しています。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','3',1)

declare @tbl2 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000030','3',2)

select * from @tbl1
INTERSECT
select * from @tbl2

left join 句を使用して同じこともできますが、INTERSECT 句を使った方がシンプルですね。

select t1.* from @tbl1 t1
left join @tbl2 t2
on t1.a_cd = t2.a_cd
and t1.b_kbn = t2.b_kbn
and t1.c_kbn = t2.c_kbn
where t2.a_cd is not null

以上、INTERSECT の簡単な使用方法でした。

SQL Server EXCEPT

EXCEPT

EXCEPT は、ユニオンクエリと呼ばれ、1つのクエリ結果から別のクリエ結果を差し引いて重複を取り除き結果を返します。
ユニオンクエリには、UNION, INTERSECT 句もあります。

ちなみに、SQL Server には、EXCEPT ALL はありません。

EXCEPT の使い方

1つの目のクエリ結果から、次のクリエ結果を差し引いて、重複を取り除き、結果を返します。

以下に簡単な例を書きます。
tble1の結果からtbl2と同じ結果(行)があるものを取り除き結果を返しています。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','3',1)

declare @tbl2 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000030','3',2)

select * from @tbl1
except
select * from @tbl2

left join 句を使用して同じこともできますが、EXCEPT 句を使った方がシンプルですね。

select t1.* from @tbl1 t1
left join @tbl2 t2
on t1.a_cd = t2.a_cd
and t1.b_kbn = t2.b_kbn
and t1.c_kbn = t2.c_kbn
where t2.a_cd is null

以上、EXCEPT の簡単な使用方法でした。