欲しいテーブル rep_id| status_1 | status_2 | status_3 | status_4 -----+----------+-----------+----------+-------- 1 |10/1 0:01 | 10/1 1:34 | null | 10/1 2:00 2 |null | 10/1 23:00 | 10/1 23:32 | 10/2 1:02 3 |null | null | null | 10/2 4:05 4 |null | 10/3 18:30 | null | null 5 |null | 10/3 20:34 | null | null 0140デフォルトの名無しさん2021/10/24(日) 00:25:59.14ID:4lHZz/Ub SQLだけでやってみた WITH ブレークチェック AS ( SELECT CASE WHEN LAG(status,1,NULL) OVER (ORDER BY [time]) < status THEN 0 ELSE 1 END AS st, [time] FROM 元のテーブル), 開始終了 AS ( SELECT [time], (SELECT MIN([time]) FROM ブレークチェック WHERE st = 1 AND [time] > t.[time]) AS end_time FROM ブレークチェック t WHERE st = 1), t1 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 1), t2 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 2), t3 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 3), t4 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 4) SELECT ROW_NUMBER() OVER (ORDER BY 開始終了.[time]) AS rep_id, t1.[time] AS status_1, t2.[time] AS status_2, t3.[time] AS status_3, t4.[time] AS status_4 FROM 開始終了 LEFT JOIN t1 ON t1.[time] >= 開始終了.[time] AND (t1.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL) LEFT JOIN t2 ON t2.[time] >= 開始終了.[time] AND (t2.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL) LEFT JOIN t3 ON t3.[time] >= 開始終了.[time] AND (t3.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL) LEFT JOIN t4 ON t4.[time] >= 開始終了.[time] AND (t4.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL) 素直にループ回すほうがいいな 0141デフォルトの名無しさん2021/10/24(日) 07:39:02.37ID:djVQHxKk>>140 ありがとうございます。SQLだけでも書けるんですね 足りないstatusを補完できないかとか集計キーを作ってからとか当初の目的と外れたところで発狂してました 0142デフォルトの名無しさん2021/11/06(土) 13:34:50.16ID:ABCltTaS 検索やら削除、CRUDをしたあとに得られるデータの命名に rowsとかitems、個々はrowやitem と考えています。 さらに個々rowにはdeleteやupdateのメソッドが使えるよう処理しています つまり純粋なデータというよりインスタンス化されています ここで疑問が。 rowという命名がしっくりこない気がします row.column名やらrow.delete()とかで使う分にはrowで良いと思いますが rowオブジェクトのクラス名をRowにするのはぴんとこないのです 何かいい命名案はないでしょうか もうItemでいいかなとも思ってます 0143デフォルトの名無しさん2021/11/06(土) 19:03:36.97ID:zfMx2fqR Row/Columnは行/列だから表(Table)に対しての名前はフィットしていると思うがな。 気に入らなければRecord/Flieldではどうかな? 0144デフォルトの名無しさん2021/11/07(日) 12:35:47.69ID:X+EdCY7G ありがとうございます >>143 一括置換しても問題なくいつでも変えられそうなのでとりあえずはRowにしときます 0145デフォルトの名無しさん2021/11/14(日) 11:47:27.97ID:wWg1lz8i 日本全国民テーブルから 年収960万円以下の世帯で、 かつ 18才未満の子供の数。
新案1: SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,target FROM sometable WHERE col>=0; explainでは32、他だと文法エラーらしいがSQLiteだと通る。(参考 https://rainbow-engine.com/sql-howto-caseresult-where/) explain query plan では 0|0|0|SCAN TABLE tags_bulk VIRTUAL TABLE INDEX 0:
新案2: SELECT col,target FROM (SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,* FROM sometable) WHERE col>=0; 他ではこう書けと言われているもの。 explainでは32で、見た目中身も同じ。explain query plan も全く同じ。 0158デフォルトの名無しさん2021/12/24(金) 19:32:06.67ID:unjC7EWw 元: SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str'; explainでは同じく32だが、見た目は違う。が、読み方が分からない。 explain query planでは以下。見た目通り2周している。 1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9: 2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11: 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
MATCHはCASEの中では使えないらしくLIKEにしている。 (Error: unable to use function MATCH in the requested contextと出る。) MATCHだとインデックスが使える分速いはずだが2周する分遅くなるのでどうなるか。
DBやアプリ構造の改善案は今のところ思いつかない。(変更は可能) カラムはそれぞれ別の管理なので、先に合わせる事は出来ない。 0159デフォルトの名無しさん2021/12/24(金) 21:34:31.41ID:unjC7EWw 新案3: UNION ALL SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION ALL SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str'; 元はUNIONにB-TREEが必要でこの分無駄だった。 explainは23と軽くなった。explain query plan は以下。 1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9: 2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11: 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) 単発で別々にクエリすればUNION ALL 分も取れるが、単に追加してるだけなのでPHP側で結合するよりは速いはず。
まあとにかくありがとう。 今回は下部構造のサマリなので、常に全文検索で問題ない。 言葉が混乱しててごめん。 0162デフォルトの名無しさん2021/12/25(土) 19:58:56.64ID:GPUeNtJx NOT MATCH が使えない。(Error: unable to use function MATCH in the requested context) ただし動的エラーで、2つ目のクエリでcol1にMATCHの後、col0にNOT MATCHのチェックで落ちるようだ。 CASEの中でも使えないし、やはりMATCHはインデックスのようだ。 ところでMATCH NOTは使える。
新案4: MATCH NOT SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION ALL SELECT 1,target FROM sometable WHERE col1 MATCH 'str -col0:str'; これでexplainは20、explain query plan は以下。 1|0|0|SCAN TABLE sometabke VIRTUAL TABLE INDEX 9: 2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11: 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)