【SQL SERVER】 四捨五入・切り捨て・切り上げについて

四捨五入・切り捨て・切り上げについての備忘録。

SQL SERVERのSQLを書いているとたまに「四捨五入」・「切り捨て」・「切り上げ」を取得時にしたい時があります。
本来生データの精度は、できる限る取得できる最大限の精度で保存したいものですが、使用する時にそこまで必要ない場合などありますし。

で、四捨五入・切り捨て・切り上げに使える関数ですが、
四捨五入には 「ROUND」
切り捨てには、「CEILING」
切り上げには、「FLOOR 」を使います。

ROUNDは、以下のようにでパラメターを3つ使います。
ROUND(四捨五入したい数値, 有効桁数[, 0 – 四捨五入、0 以外- 切捨て。 オプショナルでデフォルトは 0 ])
有効桁数は(0が1の位、1が小数点第1位、2が小数点第2位、3が小数点第3位、マイナス方向は-1であれば10の位、-2であれば100の位-3であれば1,000の位のようになります。

例えば、以下のようになります。

SELECT ROUND(1234.1234, -3) --> 1000.0000
SELECT ROUND(1234.1234, -2) --> 1200.0000
SELECT ROUND(1234.1234, -1) --> 1230.0000
SELECT ROUND(1234.1234, 0) --> 1234.0000
SELECT ROUND(1234.1234, 1) --> 1234.1000
SELECT ROUND(1234.1234, 2) --> 1234.1200
SELECT ROUND(1234.1234, 3) --> 1234.1230

3つめのパラメータを設定すると

SELECT ROUND(1555.1234, -3,0) --> 2000.0000
SELECT ROUND(1555.1234, -3,1) --> 1000.0000

FLOORとCEILINGはパラメターを1つだけで、そのまま整数値に切り捨て・切り上げしてくれます。

切り捨て(FLOOR)
例えば、以下のようになります。

SELECT FLOOR(1234.1234) --> 1234
SELECT FLOOR(1234.5678) --> 1234

FLOORとCEILINGは整数値に切り捨て・切り上げするので、小数点で切り上げ切り捨てを使うには、少し工夫が必要です。
まぁ、工夫と言っても桁を合わせてあげるだけですが。
例えば、以下のようになります。

SELECT FLOOR((1234.1234 * 10)) / 10 --> 1234.100000
SELECT FLOOR((1234.5678 * 100)) / 100 --> 1234.560000

切り上げ(CEILING)

SELECT CEILING(1234.1234) --> 1235
SELECT CEILING(1234.5678) --> 1235

SELECT CEILING(1234.1234 * 10) / 10 --> 1234.200000
SELECT CEILING(1234.5678 * 100) / 100 --> 1234.570000

以上、四捨五入・切り捨て・切り上げについての開発備忘録でした。

【SQL SERVER】 Pivot関数の使い方

今日はSQL SERVERのPivotに付いて使い方例を開発備忘録しておきます。

PivotはEXCELで良く集計に使われる関数です。
一般的にデータは正規化されて項目重複のない状態で保存されます。
例えば、売り上げは、日時、店舗CD、商品CD、個数、他と、店舗マスタ、商品マスターのような状態で保存されます。
人が見やすいデータは正規化されたデータと異なり、
例えば、同じ売り上げでも、日付、店舗名、商品名、個数、合計価格となります。

上の販売データの羅列を見せられても、人は売上状況を把握しづらいものです。
もっと言うなら、「森全体 ⇒ 森の一部 ⇒ 木 ⇒ 幹 ⇒ 枝 ⇒ 葉」のようにマクロなデータからミクロに向かう方が把握分析しやすいと言われています。

Pivotは個々の葉のレコードを寄り集めて、枝や幹、木、森の一部のような集計したデータにしてくれる関数です。
下のSQL例は、日付を行に商品を列に金額を合計した表をPivot関数を利用して返すようにしたものです。
また、PivotのIN句の中ではサブクエリが使用できないようなので、動的にSQLを作ってexecSQLで実行しています。
変数テーブルを使用しているため、execSQLでちょっと面倒になっていますが、本来はこんなことにならないので。。。勘弁してください。

declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 60)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '010', 40)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '020', 20)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '030', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 30)

insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '001', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '010', 150)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 180)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 200)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '030', 70)
 
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '001', 80)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '010', 120)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '030', 60)

select * from @tbl1

/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020])) T1 

--IN句を動的に作ろうとするとエラーになる
--select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (select distinct goods_cd from @tbl1)) as T 


--そこで変数内にSQLを書き、実行する。
--IN句を作り、@goods_listに格納する
declare @goods_list nvarchar(128)
select @goods_list=replace(REPLACE((SELECT distinct goods_cd AS [kugiri] 
                   from	@tbl1
                   for xml path ('')), '</kugiri>', '],'), '<kugiri>','[') 
set @goods_list=substring(@goods_list,0,len(@goods_list))

--実行SQLクエリ作成
--変数テーブルが読めないため、ここにも追加(本来は要らない)
declare @sql nvarchar(2048)
set @sql =
    '
	declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 60)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''010'', 40)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''020'', 20)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''030'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 30)

	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''001'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''010'', 150)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 180)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 200)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''030'', 70)
 
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''001'', 80)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''010'', 120)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''030'', 60)

	select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (' + @goods_list + ')) T1
	'
 
-- SQLクエリの実行
exec sp_executesql @sql;

以上、今日はSQL SERVERのPivotに付いて使い方例を開発備忘録でした。

【SQL SERVER】 日付の年月日だけを取り出す(convert)

SQL SERVERで日付の年月日だけを取り出す(convert)開発備忘録です。

比較的よく使う変換ですので、簡単にメモ書きしておきます。
SELECT CONVERT ( VARCHAR, GETDATE(), 111 )

111の部分は以下の通り。
111なので、日本 yyyy/mm/ddで取得できます。
11か111以外はほぼ使わないと思いますが、たまに使うことも有るかもしれないので、一式載せておきます。

2 桁の年 (yy) 年を 4 桁で表現 (yyyy) 標準 入力/出力**
0 または 100 (*) 既定値 mon dd yyyy hh:mi AM (または PM)
1 101 米国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/フランス dd/mm/yy
4 104 ドイツ dd.mm.yy
5 105 イタリア dd-mm-yy
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mm:ss
9 または 109 (*) 既定値 + ミリ秒 mon dd yyyy hh:mi:ss:mmm AM (または PM)
10 110 米国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO Yymmdd
13 または 113 (*) ヨーロッパ: 既定値 + ミリ秒 dd mon yyyy hh:mm:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
20 または 120 (*) ODBC 標準 yyyy-mm-dd hh:mi:ss (24h)
21 または 121 (*) ODBC 標準 (ミリ秒を含む) yyyy-mm-dd hh:mi:ss.mmm (24h)
126(***) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (スペースなし)
130* イスラム**** dd mon yyyy hh:mi:ss:mmm AM
131* イスラム**** dd/mm/yy hh:mi:ss:mmm AM

以上、SQL SERVERで日付の年月日だけを取り出す(convert)開発備忘録でした。

【SQL SERVER】 CASE文の使い方

CASE文を使って結果を分岐させて取得するSQL文の備忘録です。

CASE文は、一連の条件を評価して、考えられる結果式のうちの 1 つを返します。
CASE 式には 2 つの形式があります。
 単純 CASE 式では、1 つの式を一連の単純式と比較して結果を決定します。
 検索 CASE 式では、一連のブール式を評価して結果を判定します。
どちらの形式も、ELSE 引数 (省略可) をサポートしています。
CASE は、有効な式を使用できる任意のステートメントや句で使用できます。 たとえば、SELECT、UPDATE、DELETE、SET などのステートメントや、select_list、IN、WHERE、ORDER BY、HAVING などの句で使用できます。

簡単な例を記述します。

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','2',1)
 
select * from @tbl1

--例1
select a_cd,
    case b_kbn when '1' then 'OK' when '2' then 'NG' else '-' end as 判定
from
    @tbl1

--例2
select a_cd,
	case
		when b_kbn = '1' OR c_kbn = '0' then 'OK1'
		when b_kbn = '1' OR c_kbn = '1' then 'OK2'
		when b_kbn = '2' OR c_kbn = '0' then 'NG1'
		when b_kbn = '2' OR c_kbn = '1' then 'NG2'
		else '-' 
	end as 判定
from @tbl1

以上、CASE文を使って結果を分岐させて取得するSQL文の備忘録でした。

IX Web Hosting SSLを設定する(初回設定)

IX Web Hosting SSLを設定する(初回設定)

IX Web Hostingを利用し始めて2週間が経過しています。
今のところ全く問題なく稼働しており、安心しています。
何しろ安くあげるために、3年契約にしてしまい、問題が発生したらどうしよ~(´;ω;`)の状態なので。。。
ちなみに、費用は格安で3年間で2万円を少し切る程度です。為替レート次第で多少上下すると思いますが。

話を戻して、私の契約したIX Web Hostingは「Business Plus (Win)」というプランで、固定アドレスが3個無料で付いてきます。
これが一番の目当てで契約したので、本当は一つ上の「Unlimited Pro (Win)」というプランが固定アドレスが15個も付いてくるのでこれにしたかったのですが、まぁ、たぶん使わないかなって思って。

で、Let’s Encryptからもらえる無料のSSL証明書を使い、独自ドメインのSSLを設定したので、その開発備忘録です。
他のSSLベンダーからの証明書も同様にしてできるのでこの手順で問題ないと思います。

まず、IX Web HostingのHome画面から「Web Options」を選択します。

IX Web Hosting SSL設定 home画面

IX Web Hosting SSL設定 home画面

次に、「Select domain to configure hosting parameters」リストが表示されるので、SSLを設定したいドメインもしくはサブドメインを選択します。

IXWebhostingSSL設定 Select domain to configure hosting parameters

IXWebhostingSSL設定
Select domain to configure hosting parameters

次に、「Web Service」設定画面が表示されるので、「SSL Support」項目の「Import SSL Certificate」を選択します。
一度設定していると、「Edit」を選択することになります。
ここで、SSL証明書をもっておらずIX Web Hostingで契約して作成する場合はその下の項目を選択すれば良いと思います。

IXWebhostingSSL設定-Web Service設定画面

IXWebhostingSSL設定-Web Service設定画面

次に、「NEW SSL Support」画面が表示されます。

IXWebhostingSSL設定-SSL鍵情報登録

IXWebhostingSSL設定-SSL鍵情報登録

次に、予め入手しておいたSSL証明書の秘密鍵(SSL Server Private Key)とSSL Certificate(CERT/SSL証明書)をコピーして貼り付け、送信します。

IXWebhostingSSL設定-SSL Certificate情報送信

IXWebhostingSSL設定-SSL Certificate情報送信

正常に処理されると、ひとつ前の「Web Service」設定画面に戻ります。
エラーが出た場合はコピーミスかコピーした情報に間違いが有ると思われるので、エラーメッセージを確認してみてください。
正常に処理されると、下のようになります。

IXWebhostingSSL設定-SSL設定後

IXWebhostingSSL設定-SSL設定後

これで、https://でアクセスすると保護された通信になります。
ただ、更に「Edit」で「Force SSL Connection」にしても全てがHttps://になりません。
課題が残っていますが、とりあえず使えるようになったのでこれで良しとします。

以上、IX Web Hosting SSLを設定する(初回設定)の開発備忘録でした。

DNS TXTレコード確認

DNS TXTレコード確認する方法の備忘録です。

経緯は、Let’s Encryptという無料のSSLの証明書を使用しています。
機関が3か月と短いですが、独自ドメインでSSLが無料で使用できるので助かります。

で、Let’s Encryptの証明書発行に一度失敗してしまい、以来ずっと認証でつまづいています。

まず環境ですが、Let’s Encryptの証明書発行スタードメインを契約しているので、そこから発行してもらっています。
ドメインの所有者確認は、DNS認証とWeb認証があります。
で、DNS認証をしたいので、DNSにTXTレコードを追加して「所有者確認」をしてもらっています。

他でもサイトの所有証明やドメインの所有証明でDNSにTETレコードを追加する機会も多いと思います。
最初にミスをしなければ問題なく認証されると思うのですが、一度失敗すると問い合わせサーバ等に記憶が残ってしまい直ぐに再実行しても正しい内容が取得できないケースが出てきます。

で、DNS TXTレコード確認する方法ですが、
nslookup -q=txt hostname

例)itsys.co.jpのDNSに以下のTXTレコードを追
  _acme-challenge.itsys.co.jp TXT=ua_Jb9fkiajHyFxia8TI9WDnMquwxHxly6dQlLOS8g0

  コマンド
  nslookup -q=txt _acme-challenge.itsys.co.jp

  結果
  _acme-challenge.itsys.co.jp text = “ua_Jb9fkiajHyFxia8TI9WDnMquwxHxly6dQlLOS8g0”

となります。
レコードが正常に登録されていないと「_acme-challenge.itsys.co.jp を見つけられません: Non-existent domain」
などとなります。

以上、極たまにしか確認しないので忘れてしまうため、DNS TXTレコード確認する方法の備忘録を残しておきます。

Windows 海外レンタルサーバ IX Web HostingのControl Panelについて

Windows 海外レンタルサーバ IX Web HostingのControl Panelについての開発備忘録です。
IX Web Hostingに申し込んだのち、Control Panelへのアクセス情報が知らせれます。
早速、Control Panelへログインすると、下のような画面になります。

IX Web HostingのControl Panel

IX Web HostingのControl Panel

最初どこでドメインを追加するとかデータベースを追加するとか、わかりませんでした。
Domain Registrationsとか有るのですが、中身は明らかに違います。
で、しばらく眺めてやっとわかりました!

IX Web HostingのControl Panel

IX Web HostingのControl Panel

真ん中左にある「Trial」をクリっクすれば良かったのです^^
気付くまでに時間がかかりました。。。

以上、Windows 海外レンタルサーバ IX Web HostingのControl Panelについてでした。。。。くだらないけど、悩んだんだよ~

Windows 海外レンタルサーバ IX Web Hostingのトライアルに申し込む

格安のWindows レンタル サーバの「ExpressWeb」が無くなってしまうことになり、レンタル サーバを変える必要が出てきて、一時は国内のWindwos レンタル サーバを検討していたのですが、使い勝手が悪かったり、いろいろしてると費用がかさんだりと、思うようなレンタル会社がなくて、詳しく下の記事で
Windowsのレンタルサーバ探し

で、海外のWindowsレンタルサーバを探していて、「IX Web Hosting」と言う会社が良さそうなので、1週間のトライアルに申し込みました。

まずは申し込みですが、
IX Web Hosting Windows Hosting Plans
から申し込みます。

IX Web Hosting プラン画面

IX Web Hosting プラン画面

ちょっと見ずらいですが、下の方に「Still unsure? Try a 7 day trial, FREE!」って書いてあるところから申し込みます。海外サーバなので、unsureです。
ちなみに、「Unlimited Pro」も方が大規模なサイトにはお得かも。
固定IPアドレスが15個貰えるそうなので、無料の独自SSLのサイトを15持てます。
後、Domainも3つ無料です。
私の申し込んだ「Bisiness Plus」は固定IPアドレス3つにDomainは2つです。

クリックすると次の画面が開きます。

IX Web Hosting トライアル申し込み

IX Web Hosting トライアル申し込み

必要事項を適宜入力して一番下の黄色のボタンをクリックします。
ちなにみ、私は念のため?PayPalで申し込みましたのでPayPal確認画面が出ました。

IX Web hosting PayPla確認画面

IX Web hosting PayPla確認画面

次からは追加サービスの宣伝が表示されます。
私のは必要なかったので、一番下のNo Thanksを選択しました。

IX Web hosting 追加サービス確認画面

IX Web hosting 追加サービス確認画面

この後、3つか4つくらい同じような宣伝が続きますが、私は全てNo Thanksです。
最後に用意ができましたの最終確認画面がでます。

IX Web hosting 最終確認画面

IX Web hosting 最終確認画面

各操作を行う場合、上の画面に表記されているContorol Panel URLにログインすることになるのですが、Login EmailとURLはE-mailでも送られてきます。
ただし、PasswordがE-mailには記載されていません。
この画面を消してしまうと、面倒なことになりそうなので、要注意です。
初めてログインするとパスワードの変更を求められるので、必ずその場でログインするようにした方が良いです。

以上、「Windows 海外レンタルサーバ IX Web Hostingのトライアルに申し込む」の開発備忘録でした。
Contorol Panel やその他のやり方も随時、開発備忘録としてメモって行きたいと思います。
いつになるかは、わからないけど。。。

【SQL SERVER】 replace 文字列置き換え

データベースを管理していると、いろいろな理由でデータを読み替えて取得したいことがあります。
例えば、プログラム決め打ちで登録したデータが間違っていて、登録ミスが発生したいた場合には一括でデータの文字列を変えたいです。
また、安易に考えたタグ名などを変えたい時などもたまにあります。

そんな時、SQL SERVERであれば、replace関数を使います。
使い方は至って簡単です。

replace([項目],’置き換え前の文字’,’置き換え後の文字’)

例)select replace(‘abcde’,’abc’,’cba’)
結果)’cbade’

テーブルのデータを一括で置き換えたい時は、
update tableA
set [項目]=replace([項目],’前の文字’,’後の文字’)
from tableA
where 条件

などとすれば一括で文字列置き換えができます。
以上、replace 文字列置き換えの開発備忘録でした。

【SQL SERVER】 テーブルのコピー

SQL SERVERでデータを更新する時に簡易にテーブルデータのバックアップを取って置きたいことがあります。そんな場合は以下のようなSQL文を実行します。
私は日付やOLDをつけてコピーしています。

select into [DestinationTable] from [CopyTable]

DestinationTableをあらかじめ作成する必要は無くお手軽です。
ただし、CopyTableの行動とデータのみをコピーするため、キーやインデックス等のCONSTRAINT条件は付加されません。
あくまでも簡易バックアップなので。
必要がなくなったらごみになるので、コピーしたテーブルは削除しましょう。

以上、簡単なテーブルのコピーの開発備忘録でした。