none
クエリのエラー RRS feed

  • 質問

  • お世話になります.
    http://social.technet.microsoft.com/Forums/ja-JP/sqlserverja/thread/350a1569-91e4-499a-ac0e-1266cafc4c9b
    このスレッドの舌の根も乾かないうちに質問します...

    下記の3つのテーブルがあります.これらのテーブルから加重平均を求めたいのですが,集約関数のネストとON句の集計の2点で引っ掛かっています.どうしたらよいでしょうか?よろしくお願いします.

    CREATE TABLE [dbo].[T_PU_Detail](
     [Pt_ID] [int] NOT NULL,
     [PU_Location_Code] [bigint] NOT NULL,
     [PU_StartDate] [datetime] NOT NULL,
     [PU_AssessmentDate] [datetime] NOT NULL,
     [PU_NextAssessmentDate] [datetime] NOT NULL,
     [guideline_version] [nvarchar](10) NOT NULL,
     [Depth_Status] [nvarchar](2) NOT NULL,
     [Exudate_Status] [nvarchar](3) NOT NULL,
     [Size_MajorAxis] [decimal](5, 2) NOT NULL,
     [Size_MaxDiameter] [decimal](5, 2) NOT NULL,
     [Inflammation_Infection_Status] [nvarchar](3) NOT NULL,
     [Granuloma_Status] [nvarchar](3) NOT NULL,
     [Necrosis_Status] [nvarchar](3) NOT NULL,
     [Pocket_MajorAxis] [decimal](5, 2) NOT NULL,
     [Pocket_MaxDiameter] [decimal](5, 2) NOT NULL,
     [PU_picture] [varbinary](max) NULL,
     [PU_picture_path] [nvarchar](max) NULL,
     CONSTRAINT [PK_T_PU_Detail] PRIMARY KEY CLUSTERED
    (
     [Pt_ID] ASC,
     [PU_StartDate] ASC,
     [PU_Location_Code] ASC,
     [PU_AssessmentDate] ASC

    CREATE TABLE [dbo].[T_PU_Care_Detail](
     [Pt_ID] [int] NOT NULL,
     [PU_Location_Code] [bigint] NOT NULL,
     [PU_StartDate] [datetime] NOT NULL,
     [PU_Care_Date] [datetime] NOT NULL,
     [PU_Care_Detail_Number] [int] NOT NULL,
     [Stuff_ID] [int] NOT NULL,
     CONSTRAINT [PK_T_PU_Care_Detail] PRIMARY KEY CLUSTERED
    (
     [Pt_ID] ASC,
     [PU_Location_Code] ASC,
     [PU_StartDate] ASC,
     [PU_Care_Date] ASC,
     [PU_Care_Detail_Number] ASC

    CREATE TABLE [dbo].[T_DESIGNR_Exudate](
     [DESIGNR_Exudate_Status] [nvarchar](3) NOT NULL,
     [DESIGNR_Exudate_Description] [nvarchar](50) NOT NULL,
     [LowerFrequency] [float] NOT NULL,
     [UpperFrequency] [float] NOT NULL,
     [Exudate_Score] [int] NOT NULL,
     [guideline_version] [nvarchar](10) NOT NULL,
     [DESIGN_Exudate_Status] [nchar](2) NOT NULL,
     CONSTRAINT [PK_T_DESIGNR_Exudate] PRIMARY KEY CLUSTERED
    (
     [DESIGNR_Exudate_Status] ASC

      SELECT E2.DESIGNR_Exudate_Status
        FROM T_PU_Detail AS PUD1
     INNER JOIN T_PU_Detail AS PUD2
       ON PUD1.Pt_ID = PUD2.Pt_ID
      AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
      AND PUD1.PU_StartDate = PUD2.PU_StartDate
      AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
              FROM T_PU_Detail AS PUD3
                WHERE (PUD1.PU_AssessmentDate > PU_AssessmentDate)) --greatest lower bound
     INNER JOIN T_PU_Care_Detail AS PUCD1
       ON PUCD1.Pt_ID = PUD1.Pt_ID
      AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
      AND PUCD1.PU_StartDate  = PUD1.PU_StartDate
      AND PUCD1.PU_Care_Date <= PUD1.PU_AssessmentDate
      AND PUCD1.PU_Care_Date >  PUD2.PU_AssessmentDate
     INNER JOIN T_DESIGNR_Exudate AS E2
       ON E2.LowerFrequency <=((SUM(COUNT(*) * DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_date))) * 2)
            / ((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate))
             * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1))
      AND E2.UpperFrequency > ((SUM(COUNT(*) * DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_date))) * 2)
            / ((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate))
             * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1));
    --集計やサブクエリを含む式に対して集計関数を実行することはできません。
    --HAVING 句または選択リストに含まれるサブクエリの場合、および集計される列が外部参照の場合にだけ、ON 句に集計を含めることができます。
    2009年8月7日 7:40

回答

  • (4 * ('2009/8/4' - '2009/8/1') + 2 * ('2009/8/6' - '2009/8/1') + 1 * ('2009/8/7' - '2009/8/1')) / (('2009/8/8' - '2009/8/1') * ('2009/8/8' - '2009/8/1' + 1) / 2)
    検証していませんが、T_DESIGNR_Exudate を JOIN するのがクエリを複雑化し
    エラーの遠因となっているようにおもえます。
    上記式の乗算の「4」「2」「1」のところをサブクエリにしてみてはいかがでしょう。
    • 回答としてマーク じふ 2009年8月19日 7:11
    2009年8月10日 2:37
  • ON 1 = 1
    ↑見たまんま、定数1 と定数1 の比較です。
    結果が常に true になる式です。
    とりあえず全件つなげようとしてこんな書き方になってしまいました。

    列名が無効な件は、、、
    たとえば AA.ID が無効なのは
    テーブルAA にカラムID が無いためです。
    つまり、
    INNER JOIN (SELECT min(Date) MinDate FROM A) AS AA ON 1 = 1
    INNER JOIN (SELECT ID, min(Date) MinDate FROM A) AS AA ON B.ID = AA.ID
    とすれば有効です。
    ただし、
    列 'A.ID' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。
    というエラーが出てしまいます。
    IDが一意に決まらないためです。
    そこでさらに、
    INNER JOIN (SELECT ID, min(Date) MinDate FROM A group by ID) AS AA ON B.ID = AA.ID
    とすれば大丈夫です。
    • 回答としてマーク じふ 2009年8月19日 7:11
    2009年8月11日 22:57
  • ひとつの SQL で作るとややこしくなりそうでしょうか。
     ・ビューにしておく。
     ・一時テーブルを使用する。
    といった方法はいかがでしょう。

    たとえば、いったん
    12
    10
    6
    を求めたところまでで一時テーブルにしておいて
    さらに、select文で最終的に求めたい結果を得ます。

    ほかに、再帰クエリを使ってもできるかもしれません。
    • 編集済み anningo 2009年8月12日 12:10 改行訂正
    • 回答としてマーク じふ 2009年8月18日 9:36
    2009年8月12日 12:09
  • ちょっと感情的になったりしてすみませんでした.
    返信が遅くなったことを心配されたのでしたら
    遅くなったのは私的都合だけですので心配なさらないでください。

    (また、ほかのかたの意見も伺いたかったため、
    意図的に頻繁に返信しなかったときもあります。)
    ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
    推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
    仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?
    それについてはデータ量、更新量などの要素もあり一概になにがいいとはいえないかと思います。

    蛇足ですが、、、
    実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、
    本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。
    > ほかに、再帰クエリを使ってもできるかもしれません

    こちらに関しては,ちょっと思いつきません.何かヒントだけでも...
    再帰クエリを実際に使ったことがないのでヒントは無理ですが、、、
    以前、じふ様が求めているようなSQLを書こうとしたときに
    再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。
    その頃SQLには再帰クエリの構文はなかったので諦めました。

    ただ、いったん推薦しておいてなんですが、
    まだ使用例が少なすぎるかもしれないので
    仕事で使うのであればお勧めはしかねます。

    ちなみに参考にさせていただいたのは以下のようなページです。
    ↓taediumの日記 SQL Server 2005で再帰クエリ

    • 回答としてマーク じふ 2009年8月19日 7:09
    2009年8月18日 11:41

すべての返信

  • 分かりにくいので追記です.例を示します.

    PUD1.PU_AssessmentDate
    2009/8/8
    PUD2.PU_AssessmentDate
    2009/8/1

    PUCD1.PU_Care_Date    PU_Care_Detail_Number
    2009/8/7                       1
    2009/8/6                       1
    2009/8/6                       2
    2009/8/4                       1
    2009/8/4                       2
    2009/8/4                       3
    2009/8/4                       4

    求めたい式は以下です.PU_Care_Detail_NumberにMAX関数をかけるのもありかと思いますが,PUCD1.PU_Care_Dateごとのレコード数を求めた方がよいと思っています.
    (4 * ('2009/8/4' - '2009/8/1') + 2 * ('2009/8/6' - '2009/8/1') + 1 * ('2009/8/7' - '2009/8/1')) / (('2009/8/8' - '2009/8/1') * ('2009/8/8' - '2009/8/1' + 1) / 2)
    • 回答の候補に設定 Walküre 2009年8月9日 7:26
    • 回答の候補の設定解除 高橋 春樹 2009年8月10日 5:35
    2009年8月8日 3:48
  • (4 * ('2009/8/4' - '2009/8/1') + 2 * ('2009/8/6' - '2009/8/1') + 1 * ('2009/8/7' - '2009/8/1')) / (('2009/8/8' - '2009/8/1') * ('2009/8/8' - '2009/8/1' + 1) / 2)
    検証していませんが、T_DESIGNR_Exudate を JOIN するのがクエリを複雑化し
    エラーの遠因となっているようにおもえます。
    上記式の乗算の「4」「2」「1」のところをサブクエリにしてみてはいかがでしょう。
    • 回答としてマーク じふ 2009年8月19日 7:11
    2009年8月10日 2:37
  • お世話になります.

    > 上記式の乗算の「4」「2」「1」のところをサブクエリにしてみてはいかがでしょう

    今そのあたりを書いてみています.問題は結果が集合で返ることです.これを集約したいのですが,何とも...

    --(1)On first day of admission with pressure ulcers with unknown progress, a count of care records on the admission day is 'Exudate'.
    --(2)Weighted count of care records is product of count care records and coefficient which is calculated 'DATEDIFF' function.

         SELECT    CASE WHEN NOT EXISTS (SELECT *                                                            --(1)
                                           FROM T_PU_Detail AS PUD1
                                     INNER JOIN T_PU_Detail AS PUD2
                                             ON PUD1.Pt_ID = PUD2.Pt_ID
                                            AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
                                            AND PUD1.PU_StartDate = PUD2.PU_StartDate
                                            AND PUD1.PU_AssessmentDate > PUD2.PU_AssessmentDate)
                THEN    (SELECT COUNT(*)
                           FROM T_PU_Detail AS PUD1
                     INNER JOIN T_PU_Care_Detail AS PUCD1
                             ON PUD1.Pt_ID = PUCD1.Pt_ID
                            AND PUD1.PU_Location_Code = PUCD1.PU_Location_Code
                            AND PUD1.PU_StartDate = PUCD1.PU_StartDate
                            AND PUD1.PU_AssessmentDate = PUCD1.PU_Care_Date   
                       GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate)
                ELSE    (SELECT COUNT(*) * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1)    --(2)
                            FROM T_PU_Detail AS PUD1
                     INNER JOIN T_PU_Detail AS PUD2
                             ON PUD1.Pt_ID = PUD2.Pt_ID
                            AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
                            AND PUD1.PU_StartDate = PUD2.PU_StartDate
                            AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
                                                            FROM T_PU_Detail AS PUD3
                                                           WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
                     INNER JOIN T_PU_Care_Detail AS PUCD1
                             ON PUCD1.Pt_ID = PUD1.Pt_ID
                            AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
                            AND PUCD1.PU_StartDate  = PUD1.PU_StartDate
                            AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate
                       GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date, PUD2.PU_AssessmentDate)
                END AS WeightedExudateCount;

    2009年8月10日 4:21
  • お世話になります.COUNT(*)を求めるクエリを書いてみました.この後どう展開すべきか?とりあえず中間報告です.これにDATEDIFF(day, PUCD1.PU_Care_Date, PUD2.PU_AssessmentDate)をかけてみたところ,マルチパートPUD2.PU_AssessmentDateをバインドできない旨のアラートが出てクエリが失敗します.どうもうまく行きません...

      SELECT COUNT(*)
     FROM T_PU_Care_Detail AS PUCD1
       WHERE PU_Care_Date BETWEEN (SELECT MAX(PU_AssessmentDate)
             FROM T_PU_Detail AS PUD2
            WHERE PUCD1.Pt_ID = PUD2.Pt_ID
              AND PUCD1.PU_Location_Code = PUD2.PU_Location_Code
              AND PUCD1.PU_StartDate = PUD2.PU_StartDate
              AND PUCD1.PU_Care_Date > PUD2.PU_AssessmentDate)
            AND (SELECT PU_AssessmentDate
             FROM T_PU_Detail AS PUD1
               WHERE PUCD1.Pt_ID = PUD1.Pt_ID
              AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
              AND PUCD1.PU_StartDate = PUD1.PU_StartDate)
    GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date ;
    • 編集済み じふ 2009年8月11日 3:50
    2009年8月11日 3:47
  • お世話になります.DATEDIFF関数にすぐ上で求めたクエリを掛け算したものですが...これ,結局は直積を作ってしまうんじゃないかという気がします.
    結合で求めようと,サブクエリで求めようと得られる結果は同じではないでしょうか.

    結局,COUNT関数にSUM関数をかぶせる処理を何とかしないと解決しないわけで...よろしくお願いします.

      SELECT (DATEDIFF(day, PUD1.PU_AssessmentDate, PUD2.PU_AssessmentDate) + 1)
      * (SELECT COUNT(*)
          FROM T_PU_Care_Detail AS PUCD1
         WHERE PU_Care_Date BETWEEN (SELECT MAX(PU_AssessmentDate)
               FROM T_PU_Detail AS PUD2
                 WHERE PUCD1.Pt_ID = PUD2.Pt_ID
                AND PUCD1.PU_Location_Code = PUD2.PU_Location_Code
                AND PUCD1.PU_StartDate = PUD2.PU_StartDate
                AND PUCD1.PU_Care_Date > PUD2.PU_AssessmentDate)
              AND (SELECT PU_AssessmentDate
               FROM T_PU_Detail AS PUD1
                 WHERE PUCD1.Pt_ID = PUD1.Pt_ID
                AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
                AND PUCD1.PU_StartDate = PUD1.PU_StartDate)
       GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date)
        FROM T_PU_Detail AS PUD1
     INNER JOIN T_PU_Detail AS PUD2
       ON PUD1.Pt_ID = PUD2.Pt_ID
      AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
      AND PUD1.PU_StartDate = PUD2.PU_StartDate
      AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
              FROM T_PU_Detail AS PUD3
                WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate) ;
    2009年8月11日 6:56
  • 仕様がよく理解できていないので
    なんとなくこんな書き方もありますねというご参考程度に。。→肝心の select 文は下のほうにあります。

    (テーブル名、カラム名などだいぶ変形しています。。)

    IF OBJECT_ID(N'[dbo].[A]', N'U') IS NOT NULL
     DROP TABLE [dbo].[A];
    IF OBJECT_ID(N'[dbo].[B]', N'U') IS NOT NULL
     DROP TABLE [dbo].[B];
    /*DROP TABLE [dbo].[C];*/

    CREATE TABLE [dbo].[A](
     [ID] [int] NOT NULL,
     [Date] [datetime] NOT NULL,
     [Status] [nvarchar](3) NOT NULL,
     CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
    (
     [ID] ASC,
     [Date] ASC))

    CREATE TABLE [dbo].[B](
     [ID] [int] NOT NULL,
     [CDate] [datetime] NOT NULL,
     [Code] [nvarchar](3) NOT NULL,
     CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
    (
     [ID] ASC,
     [CDate] ASC,
     [Code] ASC))

    /*CREATE TABLE [dbo].[C](
     [EStatus] [nvarchar](3) NOT NULL,
     [Lower] [float] NOT NULL,
     [Upper] [float] NOT NULL,
     CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
    (
     [EStatus] ASC))*/

    insert A values (1, '2009/8/1', 'b')
    insert A values (1, '2009/8/2', 'c')
    insert A values (1, '2009/8/3', 'd')
    insert A values (1, '2009/8/4', 'e')
    insert A values (1, '2009/8/5', 'f')
    insert A values (1, '2009/8/6', 'g')
    insert A values (1, '2009/8/7', 'h')
    insert A values (1, '2009/8/8', 'i')
    insert B values (1, '2009/8/4', '1')
    insert B values (1, '2009/8/4', '2')
    insert B values (1, '2009/8/4', '3')
    insert B values (1, '2009/8/4', '4')
    insert B values (1, '2009/8/6', '5')
    insert B values (1, '2009/8/6', '6')
    insert B values (1, '2009/8/7', '7')

    select CNT, MinDate, B.CDate, sum( CNT * datediff(day, MinDate, B.CDate))
    from B
    INNER JOIN (SELECT min(Date) MinDate FROM A) AS AA ON 1 = 1
    INNER JOIN (SELECT ID, CDate, count(*) CNT FROM B group by ID, CDate) AS BB ON BB.ID = B.ID and BB.CDate = B.CDate
    group by B.CDate, CNT, MinDate

    2009年8月11日 9:28
  • お世話になります.

    非常に示唆に富んだ回答で,ショックを受けています.
    (1)結合の相手がテーブルの操作の結果のテーブルであること
    (2)集約関数の結果の相関名を引数としてさらに集約できること

    一つ分からないのですが,
    ON 1 = 1
    とは何と何を比較しているのでしょうか?
    B.ID = AA.ID
    B.CDate = AA.Date
    など書き換えてみましたがいずれも列名が無効で失敗します.
    2009年8月11日 21:02
  • ON 1 = 1
    ↑見たまんま、定数1 と定数1 の比較です。
    結果が常に true になる式です。
    とりあえず全件つなげようとしてこんな書き方になってしまいました。

    列名が無効な件は、、、
    たとえば AA.ID が無効なのは
    テーブルAA にカラムID が無いためです。
    つまり、
    INNER JOIN (SELECT min(Date) MinDate FROM A) AS AA ON 1 = 1
    INNER JOIN (SELECT ID, min(Date) MinDate FROM A) AS AA ON B.ID = AA.ID
    とすれば有効です。
    ただし、
    列 'A.ID' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。
    というエラーが出てしまいます。
    IDが一意に決まらないためです。
    そこでさらに、
    INNER JOIN (SELECT ID, min(Date) MinDate FROM A group by ID) AS AA ON B.ID = AA.ID
    とすれば大丈夫です。
    • 回答としてマーク じふ 2009年8月19日 7:11
    2009年8月11日 22:57
  • お世話になります.よく見返してみると,戻り値が期待していたものと若干異なります.

         SELECT MinDate, 
                B.CDate, 
                CNT, 
                '*', 
                DATEDIFF(day, MinDate, B.CDate), 
                '=', 
                CNT * DATEDIFF(day, MinDate, B.CDate), 
                SUM(CNT * DATEDIFF(day, MinDate, B.CDate))
         FROM B
     INNER JOIN (SELECT ID, MIN(Date) AS MinDate 
           FROM A 
       GROUP BY ID) AS AA 
             ON B.ID = AA.ID
     INNER JOIN (SELECT ID, CDate, COUNT(*) AS CNT 
           FROM B 
       GROUP BY ID, CDate) AS BB 
             ON BB.ID = B.ID 
            AND BB.CDate = B.CDate
       GROUP BY MinDate, B.CDate, CNT;
    
    

    としてみると

    2009-08-01 2009-08-04 4 * 3 = 12 48 (=4*4*3?)
    2009-08-01 2009-08-06 2 * 5 = 10 20 (=2*2*5?)
    2009-08-01 2009-08-07 1 * 6 = 6 6 (=1*1*6?)

    が返ります.期待しているのは

    2009-08-01 2009-08-04 4 * 3 = 12 12
    2009-08-01 2009-08-06 2 * 5 = 10 22 (=12 + 10)
    2009-08-01 2009-08-07 1 * 6 = 6 28 (=12 + 10 + 6)

    (といった同心円的再帰集合なのです.どうすればよいでしょうか?)

     

    追記:...えと,少し訂正.

     SUM(CNT * DATEDIFF(day, MinDate, B.CDate))

    この場合,求めるのは12+10+6=28だけですね.失礼しました.

    • 編集済み じふ 2009年8月12日 11:03
    2009年8月12日 7:59
  • ひとつの SQL で作るとややこしくなりそうでしょうか。
     ・ビューにしておく。
     ・一時テーブルを使用する。
    といった方法はいかがでしょう。

    たとえば、いったん
    12
    10
    6
    を求めたところまでで一時テーブルにしておいて
    さらに、select文で最終的に求めたい結果を得ます。

    ほかに、再帰クエリを使ってもできるかもしれません。
    • 編集済み anningo 2009年8月12日 12:10 改行訂正
    • 回答としてマーク じふ 2009年8月18日 9:36
    2009年8月12日 12:09
  • お世話になります.
    一時テーブルですか...初めて聞きます.成書を当たってみましたが,なかなかよさげですね.

    例に示していただいたコードでは,日付の範囲を求める部分でグループ化するためのキーが不足しているために,それ以上の集約ができないのではないかと思います.

    再帰クエリは,今読んでいる書籍には詳細な説明がなくてまだ分かりません.

    当初のクエリを書きなおしてみたところです.

        SELECT PUCD1.Pt_ID, 
               PUCD1.PU_Location_Code, 
               PUCD1.PU_StartDate, 
               PUD2.PU_AssessmentDate, 
               PUCD1.PU_Care_Date, 
               PUD1.PU_AssessmentDate, 
               CNT, 
               CNT * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1) * 2 
                   /((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1) 
                   * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 2)) AS WCNT
          FROM T_PU_Detail AS PUD1 
    INNER JOIN T_PU_Detail AS PUD2 
            ON PUD1.Pt_ID = PUD2.Pt_ID 
           AND PUD1.PU_Location_Code = PUD2.PU_Location_Code 
           AND PUD1.PU_StartDate = PUD2.PU_StartDate 
           AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate) 
                                           FROM T_PU_Detail AS PUD3 
                                          WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
    INNER JOIN (SELECT Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date, COUNT(*) AS CNT
                  FROM T_PU_Care_Detail
              GROUP BY Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date) AS PUCD1
            ON PUCD1.Pt_ID = PUD1.Pt_ID 
           AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code 
           AND PUCD1.PU_StartDate  = PUD1.PU_StartDate 
           AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate 
      GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
        PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT; 
    
    上記の場合,下記のキーで集約できる余地があるのではないでしょうか.
    PUCD1.Pt_ID,
    PUCD1.PU_Location_Code, 
    PUCD1.PU_StartDate,
    PUD2.PU_AssessmentDate,
    PUD1.PU_AssessmentDate
    2009年8月13日 5:08
  • お世話になります.前回,集約できそうと思ってやってみたんですが,GROUP付近の不適切な構文があるとのエラーでうまく行っていません.

     
          SELECT SUM(WCNT) AS WAVG
          SELECT PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
    	  PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT, 
    	  CNT * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1) * 2 
    	  /((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1) 
    	  * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 2)) AS WCNT, 
            FROM T_PU_Detail AS PUD1 
      INNER JOIN T_PU_Detail AS PUD2 
              ON PUD1.Pt_ID = PUD2.Pt_ID 
             AND PUD1.PU_Location_Code = PUD2.PU_Location_Code 
             AND PUD1.PU_StartDate = PUD2.PU_StartDate 
             AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate) 
    					 FROM T_PU_Detail AS PUD3 
    					WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
      INNER JOIN (SELECT Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date, COUNT(*) AS CNT
    	     FROM T_PU_Care_Detail
    	 GROUP BY Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date) AS PUCD1
              ON PUCD1.Pt_ID = PUD1.Pt_ID 
             AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code 
             AND PUCD1.PU_StartDate  = PUD1.PU_StartDate 
             AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate 
        GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
    			 PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT;
        GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
    			 PUD2.PU_AssessmentDate, /*PUCD1.PU_Care_Date, */PUD1.PU_AssessmentDate, CNT;
    2009年8月14日 0:06
  • お世話になります.その後も二つ前のクエリ全体をテーブル式にして試行していますが,構文エラーが解決しません.
    集約のためのキーは存在するのに,何故できないのかが分かりません.

         SELECT MinDate, 
                B.CDate, 
                CNT, 
                '*', 
                DATEDIFF(day, MinDate, B.CDate), 
                '=', 
                CNT * DATEDIFF(day, MinDate, B.CDate), 
                SUM(CNT * DATEDIFF(day, MinDate, B.CDate)) AS SUMCNT, 
                COUNT(CNT * DATEDIFF(day, MinDate, B.CDate)) AS CNTCNT
         FROM B
     INNER JOIN (SELECT ID, MIN(Date) AS MinDate 
                   FROM A 
               GROUP BY ID) AS AA 
             ON B.ID = AA.ID
     INNER JOIN (SELECT ID, CDate, COUNT(*) AS CNT 
           FROM B 
       GROUP BY ID, CDate) AS BB 
             ON BB.ID = B.ID 
            AND BB.CDate = B.CDate
       GROUP BY MinDate, B.CDate, CNT;
    
    



    の結果は
    2009-08-01 2009-08-04 4 * 3 = 12 48 4
    2009-08-01 2009-08-06 2 * 5 = 10 20 2
    2009-08-01 2009-08-07 1 * 6 = 6 6 1

    と返ります.SELECT句の最後のCOUNT関数の結果CNTCNTが1にならない理由も分かりません.よろしくお願いします.
    2009年8月15日 1:33
  • お世話になります.その後試行錯誤して下記コードで求めたい値にたどり着きました.

    IF OBJECT_ID(N'[dbo].[Care]') IS NOT NULL
    	DROP TABLE [dbo].[Care];
    IF OBJECT_ID(N'[dbo].[Detail]') IS NOT NULL
    	DROP TABLE [dbo].[Detail];
    
    CREATE TABLE [dbo].[Detail](
     [Pt_ID] [int] NOT NULL,
     [AssessmentDate] [datetime] NOT NULL,
     [Status] [nvarchar](3) NOT NULL,
     CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED 
    (
     [Pt_ID] ASC,
     [AssessmentDate] ASC))
    
    CREATE TABLE [dbo].[Care](
     [Pt_ID] [int] NOT NULL,
     [Care_Date] [datetime] NOT NULL,
     [Code] [nvarchar](3) NOT NULL,
     CONSTRAINT [PK_Care] PRIMARY KEY CLUSTERED 
    (
     [Pt_ID] ASC,
     [Care_Date] ASC,
     [Code] ASC))
    
    insert Detail values (1, '2009/7/30', 'a')
    insert Detail values (1, '2009/8/1', 'b')
    insert Detail values (1, '2009/8/8', 'c')
    insert Care values (1, '2009/8/4', '1')
    insert Care values (1, '2009/8/4', '2')
    insert Care values (1, '2009/8/4', '3')
    insert Care values (1, '2009/8/4', '4')
    insert Care values (1, '2009/8/6', '1')
    insert Care values (1, '2009/8/6', '2')
    insert Care values (1, '2009/8/7', '1')
    
    	 SELECT TMP.Pt_ID, TMP.MaxDate, TMP.MinDate, 
    			SUM(TMP.WCNT) AS WSUM 
    	   FROM (																
    	 SELECT	D1.Pt_ID, 
    			D1.AssessmentDate AS MaxDate, 
    			D2.AssessmentDate AS MinDate, 
    			C1.Care_Date, 
    			CNT, 
    			DATEDIFF(day, D2.AssessmentDate, C1.Care_Date) AS DIFF, 
    			CNT * DATEDIFF(day, D2.AssessmentDate, C1.Care_Date) AS WCNT 
    	   FROM	Detail AS D1
     INNER JOIN	Detail AS D2
    		 ON	D1.Pt_ID = D2.Pt_ID 
    		AND	D1.AssessmentDate > D2.AssessmentDate 
    		AND D2.AssessmentDate = (SELECT MAX(AssessmentDate) 
    								   FROM	Detail AS D3 
    								  WHERE	D1.AssessmentDate > D3.AssessmentDate) 
     INNER JOIN	(SELECT Pt_ID, Care_Date, COUNT(*) AS CNT 
    			   FROM Care 
    		   GROUP BY Pt_ID, Care_date) AS C1 
    		 ON	C1.Pt_ID = D1.Pt_ID 
    		AND	C1.Care_Date BETWEEN D2.AssessmentDate AND D1.AssessmentDate 
       GROUP BY	D1.Pt_ID, D1.AssessmentDate, D2.AssessmentDate, C1.Care_Date, CNT
    			) AS TMP
       GROUP BY	TMP.Pt_ID, TMP.MaxDate, TMP.MinDate;
    レイアウトがぐちゃぐちゃですが...テーブル式によるクエリということで,いかがでしょうか.
    2009年8月17日 8:04
  • FROM句をSELECT句にしたんですね。
    いいアイデアなのではないでしょうか。
    2009年8月18日 7:34
  • お世話になります.
    間違ってはいないようで,ありがとうございました.
    ちょっと感情的になったりしてすみませんでした.

    ここからはご意見を伺いたいのですか...

    > いったん
    > 12
    > 10
    > 6
    > を求めたところまでで一時テーブルにしておいて
    > さらに、select文で最終的に求めたい結果を得ます。

    一時テーブルではないにしても似た方針で結果は得られたかなと思います.
    ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
    推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
    仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?
    > ほかに、再帰クエリを使ってもできるかもしれません

    こちらに関しては,ちょっと思いつきません.何かヒントだけでも...
    2009年8月18日 9:35
  • ちょっと感情的になったりしてすみませんでした.
    返信が遅くなったことを心配されたのでしたら
    遅くなったのは私的都合だけですので心配なさらないでください。

    (また、ほかのかたの意見も伺いたかったため、
    意図的に頻繁に返信しなかったときもあります。)
    ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
    推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
    仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?
    それについてはデータ量、更新量などの要素もあり一概になにがいいとはいえないかと思います。

    蛇足ですが、、、
    実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、
    本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。
    > ほかに、再帰クエリを使ってもできるかもしれません

    こちらに関しては,ちょっと思いつきません.何かヒントだけでも...
    再帰クエリを実際に使ったことがないのでヒントは無理ですが、、、
    以前、じふ様が求めているようなSQLを書こうとしたときに
    再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。
    その頃SQLには再帰クエリの構文はなかったので諦めました。

    ただ、いったん推薦しておいてなんですが、
    まだ使用例が少なすぎるかもしれないので
    仕事で使うのであればお勧めはしかねます。

    ちなみに参考にさせていただいたのは以下のようなページです。
    ↓taediumの日記 SQL Server 2005で再帰クエリ

    • 回答としてマーク じふ 2009年8月19日 7:09
    2009年8月18日 11:41
  • お世話になります.

    > 実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、
    > 本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。

    そうですね.とりあえず一旦今の状態で様子を見ます.

    > 以前、じふ様が求めているようなSQLを書こうとしたときに
    > 再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。

    手続き型に近いやり方に見えますね.せっかくSQLを使うなら,集合指向で行きたいという気もします.

    ともあれ,今回はこれで解決とさせていただきます.ありがとうございました.
    2009年8月19日 7:09