gpt4 book ai didi

datediff - 在 PostgreSQL 中使用日期差异函数

转载 作者:行者123 更新时间:2023-11-29 13:29:43 27 4
gpt4 key购买 nike

我在 sql server 中使用下面的查询并且它工作正常但现在我们开始使用 Netezza 并且查询停止工作。我怀疑 datediff 函数在新西兰不起作用。我是 Netezza 的新手 - 请帮忙。这是完整的查询

SET NOCOUNT ON
GO

DECLARE @DataSource TABLE
(
[PATIENT_ID] TINYINT
,[ADMIT_DATE] DATE
,[LOCATION] VARCHAR(3)
)

INSERT INTO @DataSource ([PATIENT_ID], [ADMIT_DATE], [LOCATION])
VALUES (33, '1-10-2014', 'ER')
,(33, '1-11-2014', 'ER')
,(33, '1-15-2014', 'ER')
,(33, '1-17-2014', 'ER')
,(45, '2-15-2014', 'OBS')
,(45, '2-16-2014', 'OBS')
,(45, '2-20-2014', 'OBS')
,(45, '2-25-2014', 'OBS')
,(45, '2-27-2014', 'OBS')

;WITH TempDataSource ([PATIENT_ID], [ADMIT_DATE], [LOCATION], [Rank]) AS
(
SELECT [PATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [PATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM @DataSource
)

,DataSource ([PATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS]) AS
(
SELECT DS1.[PATIENT_ID]
,DS1.[ADMIT_DATE]
,DS1.[LOCATION]
,DATEDIFF(HOUR, DS2.[ADMIT_DATE], DS1.[ADMIT_DATE])
FROM TempDataSource DS1
LEFT JOIN TempDataSource DS2
ON DS1.[Rank] - 1 = DS2.[Rank]
AND DS1.[PATIENT_ID] = DS2.[PATIENT_ID]
AND DS1.[LOCATION] = DS2.[LOCATION]
)
SELECT [PATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [PATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM DataSource
WHERE [DIFF_IN_HOURS] >= 48

最佳答案

这就是我将 SQL Server 代码转换为在 Netezza 中工作的方式

    create temp table tmp_DataSource
(
PATIENT_ID smallint
,ADMIT_DATE timestamp
,LOCATION VARCHAR(3)
);

INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-10-2014 00:00:00', 'ER');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-11-2014 00:01:00', 'ER');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-15-2014', 'ER');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (33, '1-17-2014', 'ER');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-15-2014', 'OBS');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-16-2014', 'OBS');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-20-2014', 'OBS');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-25-2014', 'OBS');
INSERT INTO tmp_DataSource(PATIENT_ID, ADMIT_DATE, LOCATION) VALUES (45, '2-27-2014', 'OBS');

create temp table TempDataSource as
SELECT PATIENT_ID
,ADMIT_DATE
,LOCATION
,ROW_NUMBER() OVER (PARTITION BY PATIENT_ID, LOCATION ORDER BY ADMIT_DATE ASC) as rank
FROM tmp_DataSource;

create temp table DataSource as
SELECT DS1.PATIENT_ID
,DS1.ADMIT_DATE
,DS1.LOCATION
,extract(epoch from (DS1.ADMIT_DATE - DS2.ADMIT_DATE))/3600.0 as DIFF_IN_HOURS

FROM TempDataSource DS1
LEFT JOIN TempDataSource DS2
ON DS1.Rank - 1 = DS2.Rank
AND DS1.PATIENT_ID = DS2.PATIENT_ID
AND DS1.LOCATION = DS2.LOCATION;

SELECT PATIENT_ID
,ADMIT_DATE
,LOCATION
,ROW_NUMBER() OVER (PARTITION BY PATIENT_ID, LOCATION ORDER BY ADMIT_DATE ASC)
FROM DataSource
WHERE DIFF_IN_HOURS >= 48;

关于datediff - 在 PostgreSQL 中使用日期差异函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26761670/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com