01/20(土) meetup app osaka@8 で SQL Server 2022 の話しをちょっとします。 #meetupapp - お だ のスペース
結構便利になってます!
スライドは大したこと書いてないので、デモだけ載せときます。
window 関数 楽に書けるよ
over 句以降を別名(window)として、参照出来るよ
PostgreSQL にはあるみたいですが、SQL Server 2022 からサポート。
create table [成績] (
[回数] int
, [氏名] nvarchar(20)
, [教科] nvarchar(10)
, [点数] int
)
insert into [成績] ([回数], [氏名], [教科], [点数]) values
(1, N'伊藤', N'国語', 65)
, (1, N'伊藤', N'算数', 80)
, (1, N'鈴木', N'国語', 47)
, (1, N'鈴木', N'算数', 52)
, (1, N'佐藤', N'国語', 92)
, (1, N'佐藤', N'算数', 34)
, (1, N'田中', N'国語', 88)
, (1, N'田中', N'算数', 75)
, (2, N'伊藤', N'国語', 68)
, (2, N'伊藤', N'算数', 72)
, (2, N'鈴木', N'国語', 50)
, (2, N'鈴木', N'算数', 25)
, (2, N'佐藤', N'国語', 88)
, (2, N'佐藤', N'算数', 42)
, (2, N'田中', N'国語', 78)
, (2, N'田中', N'算数', 66)
, (3, N'伊藤', N'国語', 61)
, (3, N'伊藤', N'算数', 96)
, (3, N'鈴木', N'国語', 47)
, (3, N'鈴木', N'算数', 52)
, (3, N'佐藤', N'国語', 91)
, (3, N'佐藤', N'算数', 51)
, (3, N'田中', N'国語', 83)
, (3, N'田中', N'算数', 89)
select
[回数]
, [氏名]
, [教科]
, [点数]
, max([点数]) over (partition by [回数], [教科]) as [最高点]
, [点数] - max([点数]) over (partition by [回数], [教科]) as [最高点との差]
, avg([点数]) over (partition by [回数], [教科]) as [平均点]
, [点数] - avg([点数]) over (partition by [回数], [教科]) as [平均点との差]
, sum([点数]) over (partition by [氏名], [教科] order by [回数]) as [教科累計点(回数時点)]
, sum([点数]) over (partition by [氏名], [教科]) as [教科累計点(全体)]
from
[成績]
order by
[回数]
, [氏名]
, [教科]
select
[回数]
, [氏名]
, [教科]
, [点数]
, max([点数]) over [回数教科win] as [最高点]
, [点数] - max([点数]) over [回数教科win] as [最高点との差]
, avg([点数]) over [回数教科win] as [平均点]
, [点数] - avg([点数]) over [回数教科win] as [平均点との差]
, sum([点数]) over [氏名教科_累計win] as [教科累計点(回数時点)]
, sum([点数]) over [氏名教科win] as [教科累計点(全体)]
from
[成績]
window
[回数教科win] as (partition by [回数], [教科])
, [氏名教科win] as (partition by [氏名], [教科])
, [氏名教科_累計win] as ([氏名教科win] order by [回数])
order by
[回数]
, [氏名]
, [教科]
is [not] distinct from
null 気にせずに等価比較出来るやつ
create table [Null気にしない!] (
[Id] int not null
, [値] int
)
insert into [Null気にしない!] ([Id], [値]) values
(1, 100)
, (2, null)
, (3, 90)
, (4, 90)
, (5, 100)
, (6, 85)
値が 100 以外のデータを抽出
select *
from [Null気にしない!]
where [値] <> 100
or [値] is null
select *
from [Null気にしない!]
where [値] is distinct from 100
値 が @v 以外のデータを抽出
declare @v int = 100
select *
from [Null気にしない!]
where [値] <> @v
or [値] is null
set @v = null
select *
from [Null気にしない!]
where (
@v is not null
and (
[値] <> @v
or [値] is null
)
) or (
@v is null
and [値] is not null
)
declare @v int = 100
select *
from [Null気にしない!]
where [値] is distinct from @v
set @v = null
select *
from [Null気にしない!]
where [値] is distinct from @v
値が @v のデータを抽出
declare @v int = 100
select *
from [Null気にしない!]
where [値] = @v
set @v = null
select *
from [Null気にしない!]
where [値] = @v
select *
from [Null気にしない!]
where (
@v is not null
and [値] = @v
) or (
@v is null
and [値] is null
)
declare @v int = 100
select *
from [Null気にしない!]
where [値] is not distinct from @v
ここからしばらく時系列データ絡み
日時をバケットに分けてバケットの開始日時を返す
declare
@date date = '2024-01-20',
@origin date = '2024-01-01';
select
date_bucket(day, 1, @date, @origin) as "1 day",
date_bucket(day, 2, @date, @origin) as "2 days",
date_bucket(day, 3, @date, @origin) as "3 days",
date_bucket(day, 4, @date, @origin) as "4 days";
select
date_bucket(week, 1, @date, @origin) as "1 week",
date_bucket(week, 2, @date, @origin) as "2 weeks",
date_bucket(week, 3, @date, @origin) as "3 weeks",
date_bucket(week, 4, @date, @origin) as "4 weeks";
declare @datetime datetime2 = '2024-01-20 16:32:22'
declare @originDateTime datetime2 = '2024-01-20 13:00:00'
select
date_bucket(hour, 1, @datetime, @originDateTime) as "1 hour",
date_bucket(hour, 2, @datetime, @originDateTime) as "2 hours",
date_bucket(hour, 3, @datetime, @originDateTime) as "3 hours",
date_bucket(hour, 4, @datetime, @originDateTime) as "4 hours";
実例
create table [受注] (
[受注日] date
, [受注金額] int
)
insert into [受注] ([受注日], [受注金額]) values
('2024-01-01', 12000)
, ('2024-01-01', 8000)
, ('2024-01-02', 6000)
, ('2024-01-02', 6700)
, ('2024-01-03', 16000)
, ('2024-01-03', 16700)
, ('2024-01-04', 20000)
, ('2024-01-05', 18000)
, ('2024-01-11', 8000)
, ('2024-01-12', 6000)
, ('2024-01-12', 6700)
, ('2024-01-13', 16000)
, ('2024-01-13', 16700)
, ('2024-01-14', 20000)
, ('2024-01-15', 18000)
select
[受注日]
, sum([受注金額])
from [受注]
group by [受注日]
select
date_bucket(week, 1, [受注日], cast('2024-01-01' as date)) as [基準日]
, sum([受注金額]) as [合計金額]
from [受注]
group by date_bucket(week, 1, [受注日], cast('2024-01-01' as date))
select
cast(dateadd(day, (datediff(day, '2024-01-01', [受注日]) / 7) * 7, '2024-01-01') as date) as [基準日]
, sum([受注金額]) as [合計金額]
from [受注]
group by datediff(day, '2024-01-01', [受注日]) / 7
generate_series
連番生成
select [value]
from generate_series(1, 10)
;
with cte ([value]) as (
select 1 as [value]
union all
select [value] + 1
from [cte]
where [value] + 1 <= 10
)
select [value] from [cte]
;
select [value]
from generate_series(1, 50, 5)
;
with cte ([value]) as (
select 1 as [value]
union all
select [value] + 5
from [cte]
where [value] + 5 <= 50
)
select [value] from [cte]
;
欠損値の補完用に 構文 追加
create table [時系列データ] (
[日時] datetime2
, [値1] int
, [値2] int
)
insert into [時系列データ] ([日時], [値1], [値2]) values
('2023-01-16 13:00:00', 10, 8)
, ('2023-01-16 13:01:00', 5, 9)
, ('2023-01-16 13:02:00', null, 17)
, ('2023-01-16 13:03:00', null, null)
, ('2023-01-16 13:04:00', 8, 25)
, ('2023-01-16 13:05:00', 17, null)
, ('2023-01-16 13:06:00', null, null)
, ('2023-01-16 13:07:00', 13, 21)
;
select
[日時]
, [値1] as [元_値1]
, last_value([値1]) ignore nulls over (order by [日時]) as [補完後_値1]
, [値2] as [元_値1]
, last_value([値2]) ignore nulls over (order by [日時]) as [補完後_値2]
from [時系列データ]
order by [日時]
select
[日時]
, [値1] as [元_値1]
, last_value([値1]) over (order by [日時]) as [補完失敗_値1]
, lag([値1]) over (order by [日時]) as [1個前_値1]
, lag([値1], 2) over (order by [日時]) as [2個前_値1]
, [値2] as [元_値1]
, last_value([値2]) over (order by [日時]) as [補完失敗_値2]
, lag([値2]) over (order by [日時]) as [1個前_値2]
, lag([値2], 2) over (order by [日時]) as [2個前_値2]
from [時系列データ]
order by [日時]
select
[時系列データ].[日時]
, [時系列データ].[値1] as [元_値1]
, case
when [時系列データ].[値1] is not null then [時系列データ].[値1]
else (
select top(1) [nest_].[値1]
from [時系列データ] [nest_]
where [nest_].[値1] is not null
and [nest_].[日時] < [時系列データ].[日時]
order by [nest_].[日時] desc
)
end as [補完後_値1]
, [時系列データ].[値2] as [元_値2]
, case
when [時系列データ].[値2] is not null then [時系列データ].[値2]
else (
select top(1) [nest_].[値2]
from [時系列データ] [nest_]
where [nest_].[値2] is not null
and [nest_].[日時] < [時系列データ].[日時]
order by [nest_].[日時] desc
)
end as [補完後_値2]
from [時系列データ]
order by [日時]
isjson
しばらく json 絡み
json_type_constraint 追加
value / array / object / scalar
今まで(引数省略)は、object or array しか対応してなかったはず
どんな json でもいいなら、value
指定
declare @json_object nvarchar(max) = N'{"prop": "value"}'
declare @json_array nvarchar(max) = N'[{"prop": "value"}, 1, "aaa"]'
declare @json_scalar nvarchar(max) = '123'
declare @json_value nvarchar(max) = 'true'
select
N'object' as [type]
, @json_object as [json]
, isjson(@json_object) as [指定なし]
, isjson(N'{"prop"= "value"}') as [invalid]
, isjson(@json_object, value) as [value指定]
, isjson(@json_object, array) as [array指定]
, isjson(@json_object, object) as [object指定]
, isjson(@json_object, scalar) as [scalar指定]
union select
N'array' as [type]
, @json_array as [json]
, isjson(@json_array) as [指定なし]
, isjson(N'[{"prop": "value"}, 1 "aaa"]') as [invalid]
, isjson(@json_array, value) as [value指定]
, isjson(@json_array, array) as [array指定]
, isjson(@json_array, object) as [object指定]
, isjson(@json_array, scalar) as [scalar指定]
union select
N'scalar' as [type]
, @json_scalar as [json]
, isjson(@json_scalar) as [指定なし]
, isjson(N'a12', value) as [invalid]
, isjson(@json_scalar, value) as [value指定]
, isjson(@json_scalar, array) as [array指定]
, isjson(@json_scalar, object) as [object指定]
, isjson(@json_scalar, scalar) as [scalar指定]
union select
N'value' as [type]
, @json_value as [json]
, isjson(@json_value) as [指定なし]
, isjson(N'False', value) as [invalid]
, isjson(@json_value, value) as [value指定]
, isjson(@json_value, array) as [array指定]
, isjson(@json_value, object) as [object指定]
, isjson(@json_value, scalar) as [scalar指定]
order by [type]
json_path_exists
declare @json_object nvarchar(max) = N'{"プロパティ": "値", "nest": [1, "add", {"prop1": 123, "prop2": "abc" }]}'
select
json_path_exists(@json_object, N'$."プロパティ"') as 直下
, json_path_exists(@json_object, N'$.nest[2].prop1') as ネスト
, json_path_exists(@json_object, N'$.hoge') as 存在しない
, json_path_exists(N'jsonじゃない', N'$.hoge') as エラーにはならない
select json_object()
select json_object('id': [Id], '値': [値])
from [Null気にしない!]
select json_object('id': [Id], '値': [値] null on null)
from [Null気にしない!]
select json_object('id': [Id], '値': [値] absent on null)
from [Null気にしない!]
select json_object('値': [値], 'id': [Id] absent on null)
from [Null気にしない!]
select json_array()
select json_array(1, 'aaa', 2, 3, 'ddd')
select json_array(1, json_object('prop': 'value', 'items': json_array(1, 2, 3)))
approx_percentile_cont/disc
percentile_cont/disc の高速適当版
select
[回数]
, [教科]
, [点数]
, percentile_cont(0.5) within group (order by [点数]) over (partition by [回数], [教科]) as [中央値_間の平均]
, percentile_disc(0.5) within group (order by [点数]) over (partition by [回数], [教科]) as [中央値_cume_dist以上で最小]
, cume_dist() over (partition by [回数], [教科] order by [点数]) as cume_dist
from [成績]
order by
[回数]
, [教科]
select
[回数]
, [教科]
, approx_percentile_cont(0.5) within group (order by [点数]) as [中央値_間の平均]
, approx_percentile_disc(0.5) within group (order by [点数]) as [中央値_cume_dist以上で最小]
from [成績]
group by
[回数]
, [教科]
greatest / least
やっと来た、最大 or 最小
select
greatest(10, 45, null, 1, 3)
, least(10, 45, null, 1, 3)
string_split
ordinal(序数) を付加
select *
from string_split('あいう,かきく,さしす,たちつ', ',')
select *
from string_split('あいう,かきく,さしす,たちつ', ',', 1)
datetrunc
やっと来た 日付系の切り捨て
declare @d datetime2 = '2023-09-18 17:45:21.1234567'
select 'Year', datetrunc(year, @d)
union select 'Quater', datetrunc(quarter, @d)
union select 'Month', datetrunc(month, @d)
union select 'Week', datetrunc(week, @d)
union select 'Iso_week', datetrunc(iso_week, @d)
union select 'Day', datetrunc(day, @d)
union select 'Hour', datetrunc(hour, @d)
union select 'Minute', datetrunc(minute, @d)
union select 'Second', datetrunc(second, @d)
union select 'Millisecond', datetrunc(millisecond, @d)
union select 'Microsecond', datetrunc(microsecond, @d)
order by 2
ltrim / rtrim
指定文字で削除出来るようになった
declare @s nvarchar(30) = N' 前は半角スペース 後ろは全角スペース '
select
ltrim(@s)
, rtrim(@s)
select
ltrim(@s, ' ')
, rtrim(@s, ' ')
select
ltrim(N'123あいうえお321', '123')
, rtrim(N'123あいうえお321', '123')
trim
削除する方向を指定出来るように
declare @s nvarchar(30) = N'123あいうえお321'
select trim('123' from @s)
select
trim(leading '123' from @s)
, trim(trailing '123' from @s)
, trim(both '123' from @s)
left_shift / right_shift
ここからビット操作関係
select
left_shift(10, 2)
, right_shift(40, 2)
, left_shift(0x0a, 1)
, right_shift(0x25, 1)
bit_count
select
bit_count(11)
, bit_count(0x25abc)
get_bit
指定位置のビットを返す (右から0始まり)
select
get_bit(0x25abc, 2)
, get_bit(0x25abc, 10)
select
get_bit(10, 0)
, get_bit(10, 1)
, get_bit(10, 2)
, get_bit(10, 3)
, get_bit(10, 4)
, get_bit(10, 5)
, get_bit(10, 31)
set_bit
指定位置のビットを設定 (右から0始まり、デフォルト値は 1)
select
set_bit(0x0, 3)
, set_bit(10, 3, 0)