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

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′
など、さまざま表記法があります。

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