トップ回答者
クエリのエラー

質問
-
お世話になります.
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 句に集計を含めることができます。
回答
-
(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
-
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
-
ちょっと感情的になったりしてすみませんでした.
返信が遅くなったことを心配されたのでしたら遅くなったのは私的都合だけですので心配なさらないでください。(また、ほかのかたの意見も伺いたかったため、意図的に頻繁に返信しなかったときもあります。)ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?それについてはデータ量、更新量などの要素もあり一概になにがいいとはいえないかと思います。蛇足ですが、、、実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。> ほかに、再帰クエリを使ってもできるかもしれません
こちらに関しては,ちょっと思いつきません.何かヒントだけでも...再帰クエリを実際に使ったことがないのでヒントは無理ですが、、、以前、じふ様が求めているようなSQLを書こうとしたときに再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。その頃SQLには再帰クエリの構文はなかったので諦めました。ただ、いったん推薦しておいてなんですが、まだ使用例が少なすぎるかもしれないので仕事で使うのであればお勧めはしかねます。ちなみに参考にさせていただいたのは以下のようなページです。↓taediumの日記 SQL Server 2005で再帰クエリ- 回答としてマーク じふ 2009年8月19日 7:09
すべての返信
-
分かりにくいので追記です.例を示します.
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) -
(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
-
お世話になります.
> 上記式の乗算の「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; -
お世話になります.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
-
お世話になります.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) ; -
仕様がよく理解できていないので
なんとなくこんな書き方もありますねというご参考程度に。。→肝心の 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 -
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
-
お世話になります.よく見返してみると,戻り値が期待していたものと若干異なります.
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
-
お世話になります.
一時テーブルですか...初めて聞きます.成書を当たってみましたが,なかなかよさげですね.
例に示していただいたコードでは,日付の範囲を求める部分でグループ化するためのキーが不足しているために,それ以上の集約ができないのではないかと思います.
再帰クエリは,今読んでいる書籍には詳細な説明がなくてまだ分かりません.
当初のクエリを書きなおしてみたところです.
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 -
お世話になります.前回,集約できそうと思ってやってみたんですが,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;
-
お世話になります.その後も二つ前のクエリ全体をテーブル式にして試行していますが,構文エラーが解決しません.
集約のためのキーは存在するのに,何故できないのかが分かりません.
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にならない理由も分かりません.よろしくお願いします. -
お世話になります.その後試行錯誤して下記コードで求めたい値にたどり着きました.
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;
レイアウトがぐちゃぐちゃですが...テーブル式によるクエリということで,いかがでしょうか. -
お世話になります.
間違ってはいないようで,ありがとうございました.
ちょっと感情的になったりしてすみませんでした.
ここからはご意見を伺いたいのですか...
> いったん> 12> 10> 6> を求めたところまでで一時テーブルにしておいて> さらに、select文で最終的に求めたい結果を得ます。
一時テーブルではないにしても似た方針で結果は得られたかなと思います.
ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?> ほかに、再帰クエリを使ってもできるかもしれません
こちらに関しては,ちょっと思いつきません.何かヒントだけでも... -
ちょっと感情的になったりしてすみませんでした.
返信が遅くなったことを心配されたのでしたら遅くなったのは私的都合だけですので心配なさらないでください。(また、ほかのかたの意見も伺いたかったため、意図的に頻繁に返信しなかったときもあります。)ただ,このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります.
推定実行プランを表示してみると,最後あたりのソートに46%コストがかかっているようです.
仮に一時テーブルを使う場合は,最もコストのかかる部分を含めておいた方がよいのでしょうか?それについてはデータ量、更新量などの要素もあり一概になにがいいとはいえないかと思います。蛇足ですが、、、実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。> ほかに、再帰クエリを使ってもできるかもしれません
こちらに関しては,ちょっと思いつきません.何かヒントだけでも...再帰クエリを実際に使ったことがないのでヒントは無理ですが、、、以前、じふ様が求めているようなSQLを書こうとしたときに再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。その頃SQLには再帰クエリの構文はなかったので諦めました。ただ、いったん推薦しておいてなんですが、まだ使用例が少なすぎるかもしれないので仕事で使うのであればお勧めはしかねます。ちなみに参考にさせていただいたのは以下のようなページです。↓taediumの日記 SQL Server 2005で再帰クエリ- 回答としてマーク じふ 2009年8月19日 7:09
-