Sql Server 偏差値の求め方

Sql Server 偏差値の求め方

Sql Server を使って偏差値を求める SQL の書き方です。
偏差値とは、ある数値がサンプルの中でどれくらいの位置にいるかを表した無次元数。
平均値が50、標準偏差が10となるように標本変数を規格化したものです。
偏差値の利用価値が高いのは、サンプルの数値の分布が正規分布に近い状態の時で、試験などの結果に良く利用されていますね。

偏差値の求め方

偏差値の求め方ですが、
1)平均点を求める
2)平均点との差を求める
3)平均点との差の平方数を求める
4)分散を求める
5)標準偏差を求める
6)平均点との差に10をかけ標準偏差で割り+50する
以上で偏差値が求まります。

SQL で偏差値を求める

偏差値を求める 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 (20)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (70)
insert into @tbl1 (c_point) values (80)

--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--標準偏差を求める
declare @varp float
select @varp=STDEVP(c_point) from @tbl1
select @varp

--平均点との差に10をかけ標準偏差で割り+50する
declare @tbl2 table(c_point int, c_standardscore float)
insert into @tbl2 select c_point, ((c_point-@avg)*10)/@varp+50 from @tbl1
select * from @tbl2
SQLで偏差値を求めた結果

SQLで偏差値を求めた結果

以上、「Sql Server 偏差値の求め方」でした。

SQL Server 標準偏差: STDEVP, STDEV

標準偏差: STDEVP, STDEV

標準偏差とは、データの平均値から見た「ばらつき具合」を数値で表したもので、標準偏差の値が小さいほど、散らばり度合いが小さいことが示されます。

指定したデータが、全てであるデータの値がとる標準偏差(standard deviation) が欲しい場合、STDEVP 関数を使います。
指定したデータが、全体の内の「標本」(抜き出した一部分)として考え、そのデータの値がとる標本標準偏差(sample standard deviation) が欲しい場合、STDEV 関数を使います。

STDEVPの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータの数で割る(分散)
・分散の平方根を求める

STDEVの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータ-1の数で割る(*日本工業規格)(標本分散)
・標本分散の平方根を求める
以上となりますが・・・

「標準偏差」や「分散」という言葉自体をあまり聞いたことのない方にとっては、何を言っているのか、非常に難しい説明と感じることでしょう。
わかりづらいと感じた方は、次のように覚えてください。

「計算された数値が、小さいほどデータのばらつきが少ない」
という意味で、つまり、出てきた数値が「0」に近いほどばらつきの無いデータということが分かる手法になります。

標準偏差のよいところは、標準偏差は単位の次元がデータと同じなのでデータの散らばり具合が把握しやすいと言えます。

STDEVP

構文:STDEVP( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下の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 (20)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (70)
insert into @tbl1 (c_point) values (80)

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

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

-- STDEVP(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--データの数で割る
declare @varp float
select @varp=@sum/count(c_point) from @tbl2

--平方根を求める
select sqrt(@varp) as [STDEVP(c_point)検証値]
STDEVP 結果

STDEVP 結果

STDEV

構文:STDEV( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下の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 (80)

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

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

-- VAR(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--データの数で割る
--各観測値の平均値からの偏差の二乗の和を観測個数から1を引いた数で割ったばらつきの尺度
declare @var float
select @var=@sum/(count(c_point)-1) from @tbl2

--平方根を求める
select sqrt(@var) as [STDEV(c_point)検証値]
STDEV 結果

STDEV 結果

以上、SQL Server STDEV, STDEVP 標準偏差でした。

SQL Server 分散: VARP, VAR

分散: VARP, VAR

分散とは、データの平均値から見た「ばらつき具合」を数値で表したもので、数学などでは統計学や確率論にて用いられる手法の1つです。

指定したデータが、全てであるデータの値がとる分散(variance) が欲しい場合、VARP 関数を使います。
指定したデータが、全体の内の「標本」(抜き出した一部分)として考え、そのデータの値がとる標本分散 (sample variance) が欲しい場合、VAR 関数を使います。

VARPの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータの数で割る

VARの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータ-1の数で割る(*日本工業規格)
以上となりますが・・・

「分散」という言葉自体をあまり聞いたことのない方にとっては、何を言っているのか、非常に難しい説明と感じることでしょう。
わかりづらいと感じた方は、次のように覚えてください。

「計算された数値が、小さいほどデータのばらつきが少ない」

という意味で、つまり、出てきた数値が「0」に近いほどばらつきの無いデータということが分かる手法になります。

VARP

構文:VARP( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下の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 VARP(c_point) as VARP from @tbl1

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

-- VARP(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--最後にデータの数で割る
select @sum/count(c_point) as [VARP(c_point)検証値] from @tbl2
VARP 結果

VARP 結果

VAR

構文:VAR( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下の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 (80)

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

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

-- VAR(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--最後にデータの数で割る
--各観測値の平均値からの偏差の二乗の和を観測個数から1を引いた数で割ったばらつきの尺度
select @sum/(count(c_point)-1) as [VAR(c_point)検証値] from @tbl2
VAR 結果

VAR 結果

以上、SQL Server VAR, VARP 分散関数でした。

SQL Server 便利な日付関数:DATEADD DATEDIFF

SQL Server 便利な日付関数:DATEADD DATEDIFF

Sql Server は、日付の演算を行える関数を2つ実装しています。

DATEADD

DATEADDは引数に指定した日時要素*間隔分を日時に加算(減算)する関数です。
DATEADD(日時要素, 間隔, 日時)
日時要素は、以下のように指定します。
 年:year, yy, yyy
 四半期:quarter, qq, q
 月:month, mm, m
 日:dayofyear, dy, y, day, dd, d
 週:week, wk, ww
 時:hour, hh
 分:minute, mi, n
 秒:second, ss, s
 ミリ秒:millisecond, ms
 マイクロ秒:microsecond, mcs
 ナノ:nanosecond, ns

間隔は、整数値で指定します。加算ならそのまま、減算なら負の整数値で指定します。
日時は、演算を行いたい日付型データです。

以下の例は、現在日時に対し、いくつかの演算を行っています。

declare @dd datetime
set @dd = getdate()

--DATEADD
select '--基準日--' as 加算データ, @dd as 結果
union all
select '1年プラス', DATEADD(year, 1, @dd)			--1年プラス
union all
select '1年マイナス', DATEADD(year, 1, @dd)		--1年マイナス
union all
select '1月プラス', DATEADD(month, 1, @dd)			--1月プラス
union all
select '1月マイナス', DATEADD(month, -1, @dd)		--1月マイナス
union all
select '1日プラス', DATEADD(day, 10, @dd)			--1日プラス
union all
select '1日マイナス', DATEADD(day, -10, @dd)		--1日マイナス
union all
select '1時間プラス', DATEADD(hour, 1, @dd)		--1時間プラス
union all  
select '1時間マイナス', DATEADD(hour, -1, @dd)		--1時間マイナス
union all  
select '1分プラス', DATEADD(minute, 10, @dd)		--10分プラス  
union all  
select '1分マイナス', DATEADD(minute, -10, @dd)	--10分マイナス  
union all  
select '30秒プラス', DATEADD(second, 30, @dd)	--30秒プラス
union all  
SELECT '30秒マイナス', DATEADD(second, -30, @dd)	--30秒マイナス
DATEADD 結果

DATEADD 結果

DATEDIFF

DATEDIFFは、指定した日時要素単位で 2 つの日付の時間間隔を調べることができます。たとえば、2 つの日付の間の日数や、現在から年末までの週の数などを求めることができます。
DATEDIFF(日時要素, 開始日時, 終了日時, [,firstdayofweek or firstweekofyear])

下の例で @dend に設定する数値を替えいろいろ試して挙動を確認してください。

declare @dstart datetime
declare @dend datetime

set @dstart = getdate()
set @dend = dateadd(day, 100, @dstart)
select @dstart as 開始日時, @dend as 終了日時 

select 'DATEDIFF(year, @dstart, @dend)' as 式, DATEDIFF(year, @dstart, @dend) as 差
union all
select 'DATEDIFF(month, @dstart, @dend)', DATEDIFF(month, @dstart, @dend)
union all
select 'DATEDIFF(week, @dstart, @dend)', DATEDIFF(week, @dstart, @dend)
union all
select 'DATEDIFF(day, @dstart, @dend)', DATEDIFF(day, @dstart, @dend)
union all
select 'DATEDIFF(hour, @dstart, @dend)', DATEDIFF(hour, @dstart, @dend)
union all
select 'DATEDIFF(minute, @dstart, @dend)', DATEDIFF(minute, @dstart, @dend)
DATEDIFF 結果

DATEDIFF 結果

以上、「SQL Server 便利な日付関数:DATEADD DATEDIFF」でした。

SQL Server 文字列を日付型に変換する: CONVERT, CAST

SQL Server 文字列を日付型に変換する: CONVERT, CAST

SQL Serverで文字列を日付型に変換するには CONVERT, CAST を使います。
逆に日付型から文字列にする場合にも CONVERT を使います。
SQL Server 日付の年月日だけを取り出す: CONVERT

CONVERT や CAST は、あるデータ型の式を別のデータ型の式に変換できる非常に便利な関数です。

CAST

CASTを使った変換です。

select 'CAST(''02/27/2018'' AS datetime)' as 式, CAST('02/27/2018' AS datetime) as 結果
union all
select 'CAST(''180227'' AS datetime)', CAST('180227' AS datetime)
union all
select 'CAST(''18/02/27'' AS datetime)', CAST('18/02/27' AS datetime)
union all
select 'CAST(''18/02/27 07:08:09'' AS datetime)', CAST('18/02/27 07:08:09' AS datetime)
union all
select 'CAST(''20180227'' AS datetime)', CAST('20180227' AS datetime)
union all
select 'CAST(''2018/02/27'' AS datetime)', CAST('2018/02/27' AS datetime)
union all
select 'CAST(''2018/02/27 07:08:09'' AS datetime)', CAST('2018/02/27 07:08:09' AS datetime)

結果は、以下の通りです。

SQL Server CASTを使った文字列⇒日付型変換

SQL Server CASTを使った文字列⇒日付型変換

CONVERT

CONVERTを使った変換です。(CASTと変わりませんが。。。)

select 'CONVERT(datetime, ''02/27/2018'')' as 式, CONVERT(datetime, '02/27/2018') as 結果
union all
select 'CONVERT(datetime, ''180227'')', CONVERT(datetime, '180227')
union all
select 'CONVERT(datetime, ''18/02/27'')', CONVERT(datetime, '18/02/27')
union all
select 'CONVERT(datetime, ''18/02/27 07:08:09'')', CONVERT(datetime, '18/02/27 07:08:09')
union all
select 'CONVERT(datetime, ''20180227'')', CONVERT(datetime, '20180227')
union all
select 'CONVERT(datetime, ''2018/02/27'')', CONVERT(datetime, '2018/02/27')
union all
select 'CONVERT(datetime, ''2018/02/27 07:08:09'')', CONVERT(datetime, '2018/02/27 07:08:09')

結果は、以下の通りです。

SQL Server CONVERTを使った文字列⇒日付型変換

SQL Server CONVERTを使った文字列⇒日付型変換

引数の文字列表記

CONVERT も CAST も、引数にいろいろな形式で文字列を与えることができます。
これは使用する言語によってエラーになってしまう場合もあります。

使っているSql Server の設定言語に合わせて引数の文字列を渡すか、SET DATEFORMAT を使ってそのセッションの日付フォーマットを変えるかすると安心です。
基本的に日本のサーバーであれば日本語が使用言語になっていると思いますが、私は格安のアメリカのレンタルサーバーを使うこともあるため、SET DATEFORMAT を使っています。

日本は「年月日」で表記しますが

日本は「年月日」で表記しますが、アメリカは「月日年」、ヨーロッパは「日月年」となります。
セッションの言語によって、datetime の形式とシステム メッセージが決められているので、注意しましょう。

以上、「SQL Server 文字列を日付型に変換する: CONVERT, CAST」の備忘録でした。

SQL Server 言語確認

SQL Server 言語確認

SQL Server は、使用している言語に対応してメッセージなどを表示します。
言語環境が異なるといろいろ戸惑うこと、とくに日付の表記の違いとか、があるので注意が必要です。

SQL Server 使用言語の調べ方

以下のSQLで調べることができます。

select name from syslanguages where langid=(select value from sys.configurations where name='default language')

ただし、デフォルトの言語を知ることができるのみで、セッションで設定された言語は表示されません。
セッションで設定した言語の調べ方は、私も知りません。知ってたらコメントで教えてください。

エラーメッセージ

エラーメッセージの言語も設定した言語に変わります。
言語を変えて以下のようにエラーを発生させてみます。


SET LANGUAGE japanese;
GO
test
GO

SET LANGUAGE us_english;
GO
test
GO

SET LANGUAGE Italian;
GO
test

結果は、以下のようになります。

SQL Server 使用言語の違い エラー表示

SQL Server 使用言語の違い エラー表示

「メッセージ 2812、レベル 16、状態 62、行 3」のエラー情報が日本語のままですが、「ストアド プロシージャ ‘test’ が見つかりませんでした。」はちゃんと変更されてますね。

その他の関連表記

この他にも使用言語は、以下の表記にも関係してきます。
dateformat : 日付要素の順序を指定します。 有効なパラメーターはmdy、 dmy、 ymd、 ydm、 myd、およびdym。
datefirst : 週の最初の曜日を指定します。 設定が 7 なら日曜日です。
months : 言語に合わせた月名を表示。
shortmonths : 言語に合わせた短い月名を表示。
days : 言語に合わせた曜日を表示。

この言語設定に関する情報は syslanguages を参照すると見ることができます。

select * from syslanguages
SQL Server 使用言語設定表

SQL Server 使用言語設定表

この他にもまだ言語に影響される表記もありますが、とりあえずこの辺で。
以上、「SQL Server 使用言語を調べる」でした。

SQL Server 現在日時を取得する: GETDATE, SYSDATETIME

SQL Server 現在日時を取得する: GETDATE

SQL Server で、現在の日付・時刻を取得したい場合は、以下の関数で取得します。

GETDATE(). SYSDATETIME(), CURRENT_TIMESTAMP

現在のUTCの日時を取得したい場合は、以下の関数で取得します。
GETUTCDATE(), SYSUTCDATETIME()

タイム ゾーン オフセットが含まれる日時を知りたい場合は、以下の関数で取得します。
SYSDATETIMEOFFSET()

ちなみに、現在の日時と言っても実際には、SQL Servar が動いているサーバーのシステム日時を取得します。

GETDATE(). SYSDATETIME(), CURRENT_TIMESTAMP

各々、現在のデータベース システムのタイムスタンプを返します。
しかし、1 秒未満の有効桁数で比較すると、SYSDATETIME の方が GETDATE よりも高い精度を得ることができます。


select SYSDATETIME() as SYSDATETIME
, GETDATE() as GETDATE
, CURRENT_TIMESTAMP as [CURRENT_TIMESTAMP]

GETDATE(). SYSDATETIME(), CURRENT_TIMESTAMPの取得結果画面

GETDATE(). SYSDATETIME(), CURRENT_TIMESTAMPの取得結果画面

GETUTCDATE(), SYSUTCDATETIME()

各々、現在のデータベース システムのUTCのタイムスタンプを返します。
しかし、1 秒未満の有効桁数で比較すると、SYSUTCDATETIME の方が GETUTCDATE よりも高い精度を得ることができます。


select SYSUTCDATETIME() as SYSUTCDATETIME
, GETUTCDATE() as GETUTCDATE

GETUTCDATE(), SYSUTCDATETIME()の取得結果画面

GETUTCDATE(), SYSUTCDATETIME()の取得結果画面

SYSDATETIMEOFFSET()

SQL Server のインスタンスを実行しているコンピューターの日付と時刻を含む タイム ゾーン オフセットが含まれる 値を返します。


select SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET

SYSDATETIMEOFFSET()の取得結果画面

SYSDATETIMEOFFSET()の取得結果画面

誤差について

SQL Server は、GetSystemTimeAsFileTime() Windows API を使用して日付と時刻の値を取得しています。
精度は、SQL Server のインスタンスが実行されているコンピューター ハードウェアおよび Windows のバージョンによって異なります。
この API の精度は 100 ナノ秒で固定されます。
精度は、GetSystemTimeAdjustment() Windows API を使用して確認できます。

以上、「SQL Server 現在日時を取得する: GETDATE, SYSDATETIME」の紹介でした。

SQL Server 2017 Express エディション のインストール

SQL Server 2017 Express エディション のインストール

Microsoft SQL Server Express は Microsoft SQL Server の無償で提供されているエディションです。
Express は、エントリ レベルのデータベースで、最大 10 GB サイズの小規模なデータ ドリブン Web アプリケーションとモバイル アプリケーションを構築できます。

コアの最大数 4 コアです。PCでテスト環境に使うには十分かと思います。
使用できるメモリは、
インスタンスあたりの最大バッファー プール サイズで、1410 MB
インスタンスあたりの最大 Columnstore セグメント キャッシュ サイズは、352MB
データベースあたりの最大メモリ最適化データ容量は、352MB
最大データベース サイズは、10GB

となっています。
こう書かれても、私もピンと来ませんが、1つのトランザクションでそれほど大きなデータ処理をしなければ、全く問題のない機能だと思います。
もし、相当に大きなデータ処理をしたいのなら、「SQL Server 2017 Developer」を入れないといけないと思います。

SQL Server 2017 Express エディション のインストールの手順は、
1)SQL Server 2017 Express のダウンロード
2)SQL Server 2017 Express のインストール

SQL Server 2017 Express のダウンロード

下のリンク先から SQL Server 2017 Express のダウンロードを行います。
SQL Server ダウンロード

SQL Server ダウンロード

SQL Server ダウンロード

ダウンロードすると、「SQLServer2017-SSEI-Expr.exe」ファイルを入手できます。

SQL Server 2017 Express のインストール

上で入手した SQLServer2017-SSEI-Expr.exe を実行します。
インストールの準備が終わると、下のような画面が表示されます。
基本(B)をクリックします。
カスタマイズを行いたい場合は、カスタム(C)をクリックしますが、SQL Server に詳しくない場合は基本(B)を選択しましょう。

SQL Server インストール開始画面

SQL Server インストール開始画面

基本(B)をクリックすると「マイクロソフト ソフトウェア ライセンス条項」の同意画面が表示されます。
同意しましょう。

次に、SQL Server インストール場所の指定画面が表示されます。
標準ではCドライブにインストールされます。ドライブを変更したい場合は変更しておきましょう。
ここではSQL Server のプログラムのインストール先の指定なので、データの入れ先は別途後からになります。

あと、空き容量の確認をしておきましょう。
問題がなければ、「インストール(I)」ボタンをクリックしてSQL Server Express の本体のダウンロード&インストールを行います。

SQL Server インストール場所の指定

SQL Server インストール場所の指定

ダウンロード中は下のような画面が表示されています。
ダウンロードはかなりのスピードで行われるので、そんなに時間も掛からないと思います。

SQL Server インストール中の画面

SQL Server インストール中の画面

ダウンロードに成功すると下のような画面が表示されます。

SQL Server ダウンロード成功

SQL Server ダウンロード成功

ダウンロードが成功して、しばらくすると、インストールに入ります。
HDD の環境次第ですが、ダウンロードより少し時間がかかると思います。

SQL Server インストール中

SQL Server インストール中

インストールが成功すると下のような画面が表示されます。
SSMS(SQL Server Management Studio)はよく使うので、「SSMS インストール(I)」をクリックしてインストールしておきましょう。
SQL Server Management Studio (SSMS) のダウンロード画面がブラウザに表示されます。

SQL Server インストール成功

SQL Server インストール成功

SQL Server Management Studio (SSMS) のダウンロード

SSMSのダウンロードを行います。
800Mを超える大きさなので少し時間が掛かります。
「SSMS-Setup-JPN.exe」ファイルがダウンロードされます。

SSMS ダウンロード

SSMS ダウンロード

SQL Server Management Studio (SSMS) のインストール

上でダウンロードした SMS-Setup-JPN.exe を実行します。
ちなみに、SQL Server Express のインストール画面が残っていると思います。SSMSが正常にダウンロードできたのなら閉じてください。

SSMS インストール開始

SSMS インストール開始

「パッケージを読み込んでいます」と言う準備が終わると下のような画面が表示されます。
完了までにしばらくかかります。

SSMS インストール中

SSMS インストール中

完了すると下のような画面が表示されます。

SSMS インストール完了

SSMS インストール完了

SSMS を起動する

早速、SSMS を起動してみましょう。
初回はユーザー設定の読み込みとかで少し時間が掛かるかもしれませんが、下のようなログイン画面が表示されます。

SSMS ログイン画面

SSMS ログイン画面

「接続」ボタンをクリックして接続しましょう。
SSMS の画面が正常に出ましたか?
SQL Server Express に正常に接続できましたか?

SSMS 画面

SSMS 画面

「新しいクエリー」ボタンをクリックして「クエリー」画面を表示しましょう。
そこで、下の SQL を発行して正常に戻ってこれば完了です。


select * from sys.sysobjects

SSMS sysobjects画面

SSMS sysobjects画面

正常に表示されていれば、これでインストール完了です。
お疲れさまでした。

SSMSとは

SSMS は、SQL Server から SQL Database まで、SQL インフラストラクチャを管理するための統合環境です。
SSMS には、SQL のインスタンスを構成、監視、および管理するためのツールが備わっています。
SSMS を使用して、アプリケーションで使われるデータ層コンポーネントを配置、監視、アップグレードしたり、クエリとスクリプトを作成したりすることもできます。

以上、「SQL Server 2017 Express エディション のインストール」の紹介でした。

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関数を使いアイテム数や行数を求めるでした。