meetup app osaka@8 で SQL Server 2022 の話しました。 #meetupapp

01/20(土) meetup app osaka@8 で SQL Server 2022 の話しをちょっとします。 #meetupapp - お だ のスペース

結構便利になってます！
スライドは大したこと書いてないので、デモだけ載せときます。

select ... windows 句

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 [回数])
/* 参照する window で定義されていない物だけを追加出来る partition by, order by , rows or range */
/* OK
  , [回数累計win] as (order by [回数])
  , [氏名教科_累計win2] as ([回数累計win] partition by [氏名], [教科])
*/
/* NG
  [教科win] as (partition by [教科])
  , [回数教科win] as ([教科win] partition 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 -- null 省かれる
  or [値] is null

select *
from [Null気にしない!]
where [値] is distinct from 100

値 が @v 以外のデータを抽出

declare @v int = 100
select *
from [Null気にしない!]
where [値] <> @v -- null 省かれる
  or [値] is null
-- @v が null だったら？
set @v = null
/* null 以外全てになるので、↓だと意図した結果にならない。
select *
from [Null気にしない!]
where [値] <> @v -- null 省かれる
  or [値] is 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
-- @v が null だったら？
set @v = null
select *
from [Null気にしない!]
where [値]  is distinct from @v

値が @v のデータを抽出

declare @v int = 100
select *
from [Null気にしない!]
where [値] = @v -- OK

set @v = null
select *
from [Null気にしない!]
where [値] = @v -- NG

-- 両方対応しようとすると
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

date_bucket

ここからしばらく時系列データ絡み

日時をバケットに分けてバケットの開始日時を返す

declare 
    @date date = '2024-01-20',
    @origin date = '2024-01-01';
select 
    date_bucket(day, 1, @date, @origin) as "1 day", -- 1/1, 1/2, 1/3, ..., 1/20
    date_bucket(day, 2, @date, @origin) as "2 days", -- 1/1, 1/3, 1/5, 1/7, ..., 1/19, 1/21
    date_bucket(day, 3, @date, @origin) as "3 days", -- 1/1, 1/4, 1/7, ..., 1/19, 1/22
    date_bucket(day, 4, @date, @origin) as "4 days"; -- 1/1, 1/5, 1/9, ..., 1/17, 1/21
select 
    date_bucket(week, 1, @date, @origin) as "1 week", -- 1/1, 1/8, 1/15, 1/22
    date_bucket(week, 2, @date, @origin) as "2 weeks", -- 1/15, 1/29
    date_bucket(week, 3, @date, @origin) as "3 weeks", -- 1/22
    date_bucket(week, 4, @date, @origin) as "4 weeks"; -- 1/29
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", -- 13:00, 14:00, 15:00, 16:00, 17:00
    date_bucket(hour, 2, @datetime, @originDateTime) as "2 hours", -- 13:00, 15:00, 17:00
    date_bucket(hour, 3, @datetime, @originDateTime) as "3 hours", -- 13:00, 16:00, 19:00
    date_bucket(hour, 4, @datetime, @originDateTime) as "4 hours"; -- 13:00, 17:00, 21:00

実例

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 [受注日]
-- 1/1 から週単位の合計金額
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

連番生成

-- 1 ～ 10 までの連番
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]
;

-- 1 ～ 50 までで 5ずつ連番
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]
;

first_value / last_value

欠損値の補完用に 構文 追加

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)
;
-- null だったら直前のデータで補完する
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 [日時]
-- ignore nulls が無い場合… 簡易的に1個前、2個前 取る？
select
  [日時]
  , [値1] as [元_値1]
  , last_value([値1]) over (order by [日時]) as [補完失敗_値1] -- 意図した用途では使えない
  , lag([値1]) over (order by [日時]) as [1個前_値1] -- 1個前が null だったらダメ
  , lag([値1], 2) over (order by [日時]) as [2個前_値1] -- 2個前まで取る？2個前も null だったら…
  , [値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] -- true / false / null は全部小文字
  , 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 エラーにはならない

json_object

select json_object() -- 空の 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気にしない!]

json_array

select json_array() -- 空の 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 
  [回数]
  , [教科]
-- approx
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) -- enable_ordinal 引数追加

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) -- 前の半角スペース char(32) は消える
  , rtrim(@s) -- 後ろの全角スペースは消えない
select
  ltrim(@s, ' 　') -- 追加出来るようになった引数に、半角スペース、全角スペース指定
  , rtrim(@s, ' 　') -- 追加出来るようになった引数に、半角スペース、全角スペース指定

select 
  ltrim(N'123あいうえお321', '123') -- 空白以外もOK
  , rtrim(N'123あいうえお321', '123') -- 空白以外もOK

trim

削除する方向を指定出来るように

declare @s nvarchar(30) = N'123あいうえお321'
select trim('123' from @s) -- 両側から 123 を削除

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) -- 10 = 001010 を 左に2個ずらすと 101000 = 32 + 8 = 40
  , right_shift(40, 2) -- 40 = 101000 を 右に2個ずらすと 001010 = 8 + 2 = 10
  , left_shift(0x0a, 1) -- 0x0a = 0000 1010 => 0001 0100 => 0x14
  , right_shift(0x25, 1) -- 0x25 = 0010 0101 => 0001 0010 => 0x12 (アンダーフロー)

bit_count

select 
  bit_count(11) -- 10 = 1011 3個ビットが立ってる
  , bit_count(0x25abc) -- 0x25abc = 0010 0101 1010 1011 1100 10個ビットが立ってる

get_bit

指定位置のビットを返す (右から0始まり)

-- 0x25abc = 0010 0101 1010 1011 1100
select 
  get_bit(0x25abc, 2) -- 右から 3個目は 1
  , get_bit(0x25abc, 10) -- 右から 11個目は 0
-- 10 = 1010
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)
  -- , get_bit(10, 32) int 型なので、4バイト 32bit まで なので、 0 ～ 31 までしか指定出来ない

set_bit

指定位置のビットを設定 (右から0始まり、デフォルト値は 1)

select
  set_bit(0x0, 3) -- => 0x0 => 0000 0000 => 0000 1000 => 0x08
  , set_bit(10, 3, 0) -- => 10 => 1010 => 0010 => 2