グループ単位で、ある値の存在有無を判別し、重複しないIDを取得したい。 への返答

投稿で使用できる特殊コードの説明。(別タブで開きます。)
本名は入力しないようにしましょう。
投稿した後で削除するときに使うパスワードです。返答があった後は削除できません。
返答する人が目安にします。相手が小学生か社会人かで返答の仕方も変わります。
最初の投稿が質問の場合、質問者が解決時にチェックしてください。(以降も追加書き込み・返信は可能です。)
※「過去ログ」について書くときはその過去ログのURLも書いてください。

以下の返答は逆順(新しい順)に並んでいます。

投稿者 Yamato  (社会人) 投稿日時 2017/2/19 12:33:04
魔界の仮面弁士様

此度は有難うございました。
お蔭さまで希望する結果を得る事ができました。

SQL文の奥の深さに驚きました。本当に勉強になりました。
今後ともどうぞ宜しくお願い致します。


投稿者 Yamato  (社会人) 投稿日時 2017/2/19 01:08:06
魔界の仮面弁士様

ご回答有難うございました。
今、検証中ですが、希望している値を取得できそうです。
まずは御礼のみにて失礼いたします。

追伸、「直近」の定義があいまいでしたが、
実施日の降順から1回目、2回目の実施日のみ取得するという意味でした。


投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 18:57:13
訂正:

今回は日付の降順なので、ROW_NUMBER() のパーティションを
ORDER BY [実施日] から ORDER BY [実施日] DESC に
変更しておいてください。


また、先の SQL では、同日に 2 回の訪問があった場合を考慮していません。

たとえば '27-001' の ID に対して
 2016/04/07
 2016/04/05
 2016/04/07
といった多重訪問の記録があった場合に
27-001  2016/04/07  2016/04/07
としたいのならば、先の例(を DESC に修正したもの)で良いのですが、
27-001  2016/04/07  2016/04/05
としたいのであれば、『ROW_NUMBER() 関数』の代わりに
『DENSE_RANK() 関数』を使ってみてください。

―――ということで、DENSE_RANK を使ったパターンも組んでみました。


案5が CREATE TABLE 、案6が INSERT だったので、
ついでに今回は INSERT & UPDATE にしてみます。


【案7】
WITH
  [ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
  [DT_LIST] AS (
    SELECT
      [ID]
    , [実施日]
    , [行番号] = DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [実施日] DESC)
    FROM  [Aテーブル]
    WHERE [業務内容] = N'訪問'
  ),
  [RESULT] AS (
    SELECT
      [ID_LIST].[ID]
    , MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日1]
    , MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日2]
    FROM
      [ID_LIST] LEFT JOIN [DT_LIST]
      ON [ID_LIST].[ID] = [DT_LIST].[ID]
      AND [DT_LIST].[行番号] <= 2
    GROUP BY
      [ID_LIST].[ID]
  )
MERGE INTO [Bテーブル]
  USING [RESULT]
     ON ([Bテーブル].[ID] = [RESULT].[ID])
  WHEN MATCHED THEN
    UPDATE SET
      [実施日1] = [RESULT].[実施日1]
    , [実施日2] = [RESULT].[実施日2]
  WHEN NOT MATCHED THEN
    INSERT ([ID], [実施日1], [実施日2])
    VALUES (
      [RESULT].[ID]
    , [RESULT].[実施日1]
    , [RESULT].[実施日2]
    )
;
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 17:57:36
> 直近から2回分のみ取得したいです。

『直近』の定義は何ですか?
登録されている日付を、ID ごとの降順で上位 2 件拾えばよいのか、
それとも、特定の日付より前(または後)という条件が付くのか…。



> 結果をBテーブルにInsertしたいと思います。

たとえばこんな感じ。細かいところは適宜調整して下さい。
CTE 式(WITH ブロック)の代わりに、副問い合わせを使っても良いかな。

【B テーブルを新規作成】

WITH
  [ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
  [DT_LIST] AS (
    SELECT
      [ID]
    , [実施日]
    , [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
    FROM  [Aテーブル]
    WHERE [業務内容] = N'訪問'
  )
SELECT
  [ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日1]
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日2]
INTO
  [Bテーブル]
FROM
  [ID_LIST] LEFT JOIN [DT_LIST]
  ON [ID_LIST].[ID] = [DT_LIST].[ID]
  AND [DT_LIST].[行番号] <= 2
GROUP BY
  [ID_LIST].[ID]




【既存の B テーブルに追加】

WITH
  [ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
  [DT_LIST] AS (
    SELECT
    [ID]
  , [実施日]
  , [行番号] = ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [実施日])
   FROM [Aテーブル]
   WHERE [業務内容] = N'訪問'
  )
INSERT INTO [Bテーブル] ([ID], [実施日1], [実施日2])
SELECT
  [ID_LIST].[ID]
, MAX(CASE [行番号] WHEN 1 THEN [実施日] END)
, MAX(CASE [行番号] WHEN 2 THEN [実施日] END)
FROM
  [ID_LIST] LEFT JOIN [DT_LIST]
  ON [ID_LIST].[ID] = [DT_LIST].[ID]
  AND [DT_LIST].[行番号] <= 2
GROUP BY
  [ID_LIST].[ID]

投稿者 Yamato  (社会人) 投稿日時 2017/2/17 17:23:31
 魔界の仮面弁士様

お世話になっております。
このようなSQL文が有るとは、勉強になりました!有難うございます。

全て試した結果、【案4改】が素晴らしく、訪問は1回と想定していたところ、実際は複数回
実施している事が判明しました。思慮が足りませんでした。
まさしく以下のような結果となりました。

 ┏━━━┯━━━━━━━━━━━━━━━━━
 ┃ID       │実施日                          
 ┣━━━┿━━━━━━━━━━━━━━━━━
 ┃27-001│2016/04/07,2016/04/05,2016/03/10 
 ┠───┼─────────────────
 ┃27-002│                                    
 ┠───┼─────────────────
 ┃27-003│2016/06/05                    
 ┠───┼─────────────────
 ┃27-004│                                                      
 ┗━━━┷━━━━━━━━━━━━━━━━━                

そこで、Aテーブルから取得した結果をBテーブルにInsertしたいと思います。
訪問日が3回存在したとしても、直近から2回分のみ取得したいです。
厚かましいお願いですが、引き続きご教示いただけますでしょうか。
どうぞ宜しくお願い致します。

【Bテーブル】
------------------------------------
ID           実施日1      実施日2
------------------------------------
27-001    2016/04/07   2016/04/05
------------------------------------
27-002
------------------------------------
27-003    2016/06/05
------------------------------------
27-004
------------------------------------



投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 11:22:01
【案4改】 スペース区切りだと読みにくいので、全角カンマ区切りにしてみました。

SELECT DISTINCT
  [ID], REPLACE(
  ( SELECT [X].[実施日] AS [data()]  FROM [Aテーブル] [X]
    WHERE [X].[ID] = [Y].[ID]
    AND [X].[業務内容] = N'訪問'
    ORDER BY [X].[実施日]
    FOR XML PATH('')
  ), ' ', ',') AS [実施日]
FROM [Aテーブル] Y
ORDER BY [ID]



『','』のところを『CHAR(13)+CHAR(10)』に差し替えれば、改行区切りにもできます。
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 11:09:05
【案3】

WITH [CTE] AS (
SELECT DISTINCT [ID], [実施日], [業務内容] FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT [ID], [実施日], [業務内容] FROM CTE UNION
SELECT [ID], NULL, NULL FROM [Aテーブル] WHERE NOT EXISTS(
SELECT * FROM [CTE] WHERE [CTE].[ID] = [Aテーブル].[ID])
ORDER BY [ID]



【案4】

SELECT DISTINCT
  [Y].[ID], (
  SELECT [X].[実施日] + ' ' FROM [Aテーブル] [X]
   WHERE [X].[ID] = [Y].[ID]
   AND [X].[業務内容] = N'訪問'
   ORDER BY [X].[実施日]
   FOR XML PATH('')
  ) AS [実施日]
FROM [Aテーブル] Y



同一 ID で複数日の訪問があった場合、結果の ID が重複しないよう、
案4では下記のような結果を返すようにしてみました。

┏━━━┯━━━━━━━━━━━┓
┃ID    │実施日                ┃
┣━━━┿━━━━━━━━━━━┫
┃27-001│2016/04/05 2016/04/07 ┃
┠───┼───────────┨
┃27-002│                      ┃
┠───┼───────────┨
┃27-003│2016/06/05            ┃
┠───┼───────────┨
┃27-004│                      ┃
┗━━━┷━━━━━━━━━━━┛
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 10:34:18
> ・テーブル名:Aテーブル
> ID:nchar(6)
> 実施日:nchar(10)
> 業務内容:nvarchar(20)
主キーはどれとどれの組み合わせですか?


> ★以下のようにIDのグループ単位で「訪問」が含まれるIDと「訪問」が含まれないIDを取得したい。
「訪問」限定なら、結果に業務内容の列は不要な気が。


> SQL文でどのよに書けば良いかご教示頂けますと大変たすかります。

【案1】

WITH
  [ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
  [DT_LIST] AS (SELECT [ID], [実施日] /*, [業務内容] */ FROM [Aテーブル] WHERE [業務内容] = N'訪問')
SELECT
  [ID_LIST].[ID], [実施日] /* , [業務内容] */
FROM
  [ID_LIST] LEFT JOIN [DT_LIST] ON [ID_LIST].[ID] = [DT_LIST].[ID]
ORDER BY
  [ID_LIST].[ID]



【案2】

SELECT
  [ID]
, MAX(CASE [業務内容] WHEN N'訪問' THEN [実施日] END) AS [実施日]
--, MAX(CASE [業務内容] WHEN N'訪問' THEN [業務内容] END) AS [業務内容]
FROM
  [Aテーブル]
GROUP BY
  [ID]
ORDER BY
  [ID]



なお上記案は、元データに
 INSERT INTO [Aテーブル] VALUES (N'27-001', N'2016/04/07', N'訪問')
が追加された場合、両者の結果が異なることに注意して下さい。
投稿者 Yamato  (社会人) 投稿日時 2017/2/16 22:11:34
(環境)
VB.Net
  SQL Server2008

いつも勉強させていただいております。
宜しくお願いいたします。

SQL Server2008に以下のテーブルがあります。

・テーブル名:Aテーブル
ID:nchar(6)
実施日:nchar(10)
業務内容:nvarchar(20)
---------------------------------
ID            実施日     業務内容
---------------------------------
27-001     2016/04/05    訪問
27-001     2016/04/06    電話
27-001     2016/05/01    その他
-----------------------------
27-002    2016/04/06    電話
27-002    2016/05/01    その他
-----------------------------
27-003    2016/06/05    訪問
27-003  2016/06/10  電話
-----------------------------
27-004    2016/04/10    診断
27-004  2016/05/15  電話
-----------------------------

(実現したい事)
★以下のようにIDのグループ単位で「訪問」が含まれるIDと「訪問」が含まれないIDを取得したい。
   IDの人のところに、いつ訪問したのか。
   IDの人のところに、まだ訪問していないのかを集計したい。
  IDは重複しない。
---------------------------------
ID            実施日     業務内容
---------------------------------
27-001     2016/04/05    訪問
27-002
27-003     2016/06/05    訪問
27-004

SQL文でどのよに書けば良いかご教示頂けますと大変たすかります。
宜しくお願い致します。