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

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

ただいまの
回答率

89.87%

IDごとに日付の歯抜けを修正したい

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 363

sysder

score 13

以下のtable_Aがあります。

dt id value
2019-01-03 a 1
2019-01-05 a 2
2019-01-02 b 1
2019-01-04 b 2

これを以下のtable_Bのようにしたいです。

dt id value
2019-01-03 a 1
2019-01-04 a null
2019-01-05 a 2
2019-01-02 b 1
2019-01-03 b null
2019-01-04 b 2
2019-01-05 b null

僕が考えた方法は、以下のSQLで日付の連番のテーブル(テーブル名:table_dt)を作って、

SELECT
   ((SELECT MIN(dt) FROM table_A)::date + (ROW_NUMBER() OVER()) - 1)::date AS dt
FROM
    table_A
limit
    3

以下のSQLで日付とIDのすべての組み合わせを作成して(テーブル名:dt_id)、

SELECT
  dt,
  id
FROM
  table_dt
  CROSS JOIN
    (SELECT
       DISTINCT(id)
     FROM
       table_A
    )

以下のSQLでdt_idとtable_Aをleft joinでつないで歯抜けをなくす方法です。

SELECT
  t1.dt,
  t1.id,
  t2.value
FROM
  dt_id t1
  LEFT JOIN
    table_A t2
  USING(dt, id)

しかし、この方法はcross joinを使っているためIDが何百万もあって365日分を求めるとものすごく遅くなってしまいます。
また、必要のない(id=a dt=2019-01-02 value=null)のレコードができてしまいます。
別の方法はないでしょうか。
ご教示の程よろしくお願いいたします。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/07/18 01:10

    いい加減にどのデータベースか明示する習慣を身に着けては? https://teratail.com/questions/139116

    キャンセル

  • sysder

    2019/07/18 15:16

    この質問には関係ないですね。

    キャンセル

回答 2

0

where句で value is not null とか指定すればよいのではないでしょうか。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/07/19 01:06

    ご回答ありがとうございます。
    僕もそれは検討したのですが、IDごとに日付の歯抜けをなくすには至らなかったため質問させていただきました。

    キャンセル

0

必要のない(id=a dt=2019-01-02 value=null)のレコードができてしまいます。

この条件ならなぜid=bのdt=2019-01-05は抽出しているのでしょうか?

一応無理やりやってみました

create table tbl(pk int primary key auto_increment,dt date,id varchar(10),value int,unique(dt,id));
insert into tbl(dt,id,value) values
('2019-01-03','a',1),
('2019-01-05','a',2),
('2019-01-02','b',1),
('2019-01-04','b',2);

/* カレンダー*/
create table cale(pk int primary key auto_increment,dt date);
insert into cale(dt) values
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-06'),
('2019-01-07');
  • 抽出
select t2.dt,t2.id,t1.value from tbl as t1
right join (
select 'a' as id,dt from cale as t3
where exists
(select 1 from tbl where id='a' having
t3.dt between min(dt) and max(dt))
union all
select 'b' as id,dt from cale as t4
where exists
(select 1 from tbl where id='b' having
t4.dt between min(dt) and max(dt))
) as t2 on t1.id=t2.id and t1.dt=t2.dt
order by id,dt

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/07/19 01:27

    ご回答ありがとうございます。
    >この条件ならなぜid=bのdt=2019-01-05は抽出しているのでしょうか?
    日付の連番テーブルを作成で、table_Aの最小のdtにプラス3日分の日付の連番を作成しているので、2019-01-02から2019-01-05の日付の連番が作成されます。
    その日付の連番をキーにしてリレーションを行うので2019-01-05も抽出されてしまいます。

    抽出のSQLを拝見しました。IDを一つ一つ指定しているのですが、IDが何百万もあると厳しいと思いました。
    IDをインプットにする関数を作成して繰り返しの処理をすれば解消できるのでしょうか。

    キャンセル

  • 2019/07/19 09:15

    > 最小のdtにプラス3日分の日付の連番

    それはつまり・・・
    各idごとにその最小日から全体の最小日から3日間後までという
    ちょっと意味不明な処理で大丈夫ですか?
    それならなんとかなりそうな気がしますが・・・

    キャンセル

  • 2019/07/21 00:10 編集

    返信が遅くなり申し訳ありません。
    >各idごとにその最小日から全体の最小日から3日間後までという
    >ちょっと意味不明な処理で大丈夫ですか?
    おっしゃる通り意味不明と思われるかもしれません。データを無駄に増やしているだけなので…
    しかし使っているBIツールの仕様に合わせるために、致し方なくやらなければならない処理になります。

    キャンセル

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

  • ただいまの回答率 89.87%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる