質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

2回答

1048閲覧

MS SQL Serverの間引き

pumaster

総合スコア15

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2018/11/06 13:55

前提・実現したいこと

MS SQL Serverのテーブルに以下のとおり
時系列で回転数の変化を計測した情報を保存しています。

[Time]            | [rpm]
2013-04-16 11:11:00.0000000| 100
2013-04-16 11:11:10.0000000| 250
2013-04-16 11:11:20.0000000| 400
2013-04-16 11:11:30.0000000| 600
:
2013-04-16 11:15:30.0000000| 2800
2013-04-16 11:15:40.0000000| 3000
2013-04-16 11:15:50.0000000| 3010
2013-04-16 11:16:00.0000000| 3005
:
2013-04-16 11:20:00.0000000| 800
2013-04-16 11:20:10.0000000| 620
2013-04-16 11:20:20.0000000| 320
2013-04-16 11:20:30.0000000| 100

このテーブルから指定の回転数毎に間引いたレコードのみ返すクエリーを書きたいと思います。
例えば間引き回転数を400ピッチに指定すると、[100,400・・2800・・800,620,100]のレコードが返るようなクエリです。

時刻で間引くのであれば、時分秒を丸めるような方法がこちらにも掲載されていましたが、
繰り返し登場するようなデータなので、1レコードずつ読み込んで処理する以外方法はないのでしょうか。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

間引く条件が rpm が特定の数値の倍数の周辺である、ならば、

SQL

1select * from table where ABS((rpm % 400)-200) >= (200-5);

で行けそうな気もしますが、ことはそう単純ではないのですかね。


上の例は 400±5 の範囲で引っかかります。
例えば rpm = 396 のレコードがあった場合、rpm % 400 -200 = 196 ですから、195 より大きいので成立。
同様に rpm = 403 のレコードの場合、rpm % 400 - 200 = -197 で、絶対値が 197 になり成立。

投稿2018/11/07 02:10

tacsheaven

総合スコア13703

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

pumaster

2018/11/07 05:14

検討戴きありがとうございます。 データの中身についてですが、倍数周辺のデータが何件あるか分からないというのが難しい点かと思います。 回転数は上昇した後に下降しますので、この上昇時と下降時の回転数が混在しないようにする必要があります。
tacsheaven

2018/11/07 06:02

取ってくるデータの Time をもとに時系列順に並べてあれば問題ないのでは?
guest

0

ベストアンサー

1レコードずつ読み込んで処理する以外方法はないのでしょうか。

特定のピッチ間隔のデータがあればそれと結合する事で目的は果たせます。
この元になるデータを返却してくれる関数が他のDBMSには存在するのですが、残念ながらT-SQLには存在しません。

これを自作されている方もおられます。
SQL Server TVF(テーブル値) - 関連生成関数 -

こういった関数を使用せず、それでも一括で行うなら、テンポラリーテーブルを特定のピッチ刻みで作成する事になるかと思います。
※ユニオンクエリーを行数分動的に作成するという、スマートではない方法も考えられますが。

追記

rpmの最大をピークとして、ピークまでとピーク以降に分割、rpmはピッチ毎に分割し、
そのデータ内の最大のrpmのデータに間引く。(※前提として連続して同じrpmは無し
試してはいませんが、上記仕様で組み立てたSQLです。

SQL

1with base as ( 2 select *, (select max(time) from tbl where rpm =(select max(rpm) from tbl)) as peak_time 3 from tbl 4) 5, blocking as ( 6 select *, case when time<=peak_time then 0 else 1 end as peak_block, FLOOR(rpm / 400) as rpm_block 7 from base 8) 9select * 10from blocking src 11where time = ( 12 select max(time) from tbl 13 where rpm=(select max(rpm) from blocking where peak_block=src.peak_block and rpm_block=src.rpm_block) 14 )

追記2

lagを使用した別パターン

SQL

1with blocking as ( 2 select * 3 , case when rpm - lag(rpm, 1, 0) over(order by time) >= 0 then 0 else 1 end as peak_block 4 , FLOOR(rpm / 400) as rpm_block 5 from tbl 6) 7select * 8from blocking src 9where time = ( 10 select max(time) from tbl 11 where rpm=(select max(rpm) from blocking where peak_block=src.peak_block and rpm_block=src.rpm_block) 12 )

投稿2018/11/06 15:17

編集2018/11/07 12:09
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

pumaster

2018/11/07 00:32

ご回答ありがとうございます。 特定のピッチ間隔のデータと結合する方法について実現性を検討したのですが、 例えば400rpmピッチで[0,400,800・・・]の仮テーブルを作成し、回転数を条件に結合をしたとしても、 各ピッチにピタリと一致する回転数がデータテーブル内に存在事は稀で、大抵の場合399とか401のように僅かにずれた値で保存されています。 この場合間引き回転数の区分を超えた401になるデータを取り出すようにしたいのですが、この辺りの処理をSQLクエリで実現するのは難しいのではないかと考えております。
sazi

2018/11/07 00:38

基準となる表をピッチの範囲(0,400),(400,800)・・・内のデータの回転数の最大か最小を取得するようにすれば良いかと。
sazi

2018/11/07 02:16 編集

rpmについてピークを持っているデータについてが要件であることに気付いていませんでした。 それから、基準の表はいらない気がしてきました。
pumaster

2018/11/07 06:10

ご提案ありがとうございます。 追記のSQLを実行してみましたが、下記のメッセージが表示されました。 ======================== メッセージ 102、レベル 15、状態 1、行 6 '<' 付近に不適切な構文があります。 メッセージ 102、レベル 15、状態 1、行 12 ',' 付近に不適切な構文があります。 ======================== 不等号とWhere句で出ているようですが原因が良くわかりません。
sazi

2018/11/07 06:38 編集

修正してみました。手打ちしただけなので他にも不備があるかもしれません。
pumaster

2018/11/07 07:33 編集

適当に作ったテーブルで動かしてみましたが良い感じではないかと思います。 [time] [rpm] [peak_block][rpm_block] 2014-06-28 08:56:00 250 0 0.625 2014-06-28 08:56:10 550 0 1.375 2014-06-28 08:56:20 720 0 1.8 2014-06-28 08:56:30 860 0 2.15 2014-06-28 08:56:40 1000 0 2.5 2014-06-28 08:56:50 1110 0 2.775 2014-06-28 08:57:00 1080 0 2.7 2014-06-28 08:57:10 1200 0 3 2014-06-28 08:57:20 920 1 2.3 2014-06-28 08:57:30 680 1 1.7 2014-06-28 08:57:40 450 1 1.125 2014-06-28 08:57:50 210 1 0.525 2014-06-28 08:58:00 150 1 0.375 気になった点が1か所ありました。 もしもMaxになるrpmが重複していた場合、エラーになるようです。下記の通り・・・・ =============== メッセージ 512、レベル 16、状態 1、行 3 サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。 ===============
sazi

2018/11/07 07:48 編集

前提として追記していましたが、やはりそういうデータがあるんですね。 でしたら、さらにネストが必要で、timeのmaxなどで限定する必要があります。 rpm_blockは小数点になっているのは宜しくないですね。商にならないと駄目なんで。
pumaster

2018/11/07 07:47

失礼しました。前提を見落としておりました。
sazi

2018/11/07 07:50

因みに、rpmの線形は放物線状ですか? 山谷が複数あるようだと、そういったところの考慮も必要になります。
pumaster

2018/11/07 08:34

データの回転数の形状は放物線というよりも台形型になります。/ ̄\ 最高回転数に到達後しばらく回転数は維持します。 間引きの目的は、一定の回転数の間のデータを除外したいのと、 昇降中のデータも全部はいらないので適当な間隔に減らしたい という2点となります。
sazi

2018/11/07 12:10

変更しました。
pumaster

2018/11/07 14:01 編集

追記2にて確認した結果です。 (Δrpm = 400) 間引き前のデータと間引き後のデータを並べていますが、期待通りと言える結果になりました。 time rpm rpm(decimate) 00:00.0 60 00:01.0 120 00:02.0 180 00:03.0 240 00:04.0 300 00:05.0 360 360 00:06.0 420 00:07.0 480 00:08.0 540 00:09.0 600 00:10.0 660 00:11.0 720 00:12.0 780 780 00:14.0 840 00:15.0 900 00:16.0 960 00:17.0 1020 00:18.0 1080 00:19.0 1140 1140 00:20.0 1200 00:21.0 1260 00:22.0 1240 00:23.0 1280 1280 00:24.0 1270 1270 00:25.0 1260 00:26.0 1200 00:27.0 1100 1100 00:28.0 1000 00:29.0 800 00:30.0 600 600 00:31.0 400 00:32.0 200 200 どうもありがとうございました。
pumaster

2018/11/07 14:12

因みに余談ですが、データに山谷が複数あるデータも少なからず存在するようなので、 もしもそれに対応しているSQLクエリがあれば最強なのですが、作る事は可能でしょうか。 peak_blockを0,1以外に増やせば対応できますかね。
sazi

2018/11/07 14:35

with recursive(再帰)を使用して、peak_blockを生成すればある程度は可能かもしれません。
pumaster

2018/11/08 00:27

なるほど。 山1つは1日分のデータなので、2日、3日とデータを一括処理する場合には山谷が複数になってしまうというのがあります。_/ ̄\_/ ̄\_/ ̄\_ 再帰処理について時間をみつけてトライしてみます。 ありがとうございました。
sazi

2018/11/08 00:54 編集

提案ですが、rpmの間隔ではなくて、時間間隔+ピーク分を出力というのはどうでしょう。 時間はformatで秒を判断するなどして、lagとleadを使えば傾きが分かるので、ピークとするかどうかは、その傾きの大きさで判断すれば良いですし。
sazi

2018/11/08 08:27

ついでに、上記の時間間隔(スケール)については最初の回答で示した基準表を使えばいいと思います。 尚、基準表はwith recursiveを使用しても生成できますので、問い合わせのSQLのみで完結します
pumaster

2018/11/08 10:36

色々考えて戴いて済みません。 ご提案あまりイメージが出来ていないのですが、その方法だと一定回転数の期間もデータを取得してしましませんか。_/ ̄\_の_と ̄の期間。回転数は±1~5rpmくらいは上下するかと思います。 この期間を取らないように回転数の間隔を条件にいれていました。
sazi

2018/11/08 10:47

自分がグラフを作るならという考えなので、要件に合わないようでしたら、スルーして下さい。 「±1~5rpm」の幅はピークとしない事は、傾きで判断できるので可能だと思います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問