小贝子编程

SQL Server 2012使用ROW_NUMBER()OVER PARTITION删除重复项



一段时间以来，我一直在尝试使用row_number()来解决数据复制的问题，但我无法使其发挥作用。我有一堆记录，都是同一个日期，但时间不同，所以我每天只想要前两个日期实例(每个实例实际上是半天(，我可以忽略其他实例。

下面的SQL几乎满足了我的要求，但只给了我预期的一半行

SELECT
Trade.tradedate AS TradeDate,
Trade.status,
contract.contract AS ContractID,
'obsolete' AS ZainetRef,
fee.feemode,
position.counterparty,
position.positiontype,
Trade.cstcontractstart AS ContractStartDate, 
Trade.cstcontractend AS ContractEndDate,
contract.contracttype,
CONVERT(INT, Trade.trade) AS TradeID,
feetimeperiod.feetimeperiod,
feetimeperiod.dbcolumn,
feetimeperiod.dbvalue,
(CONVERT(TIME, feetimeperiod.begtime)) AS TheTime,
feetimeperiod.begtime AS FeeTimePeriodBeginTime,
feetimeperiod.endtime AS FeeTimePeriodEndTime,
loadshapeprofile.begtime AS StartDateTime,
loadshapeprofile.endtime AS EndDateTime,
loadshapeprofile.offset,
loadshape.timeunit,
CASE 
WHEN CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) = 1 
THEN 'D' 
WHEN CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) > 1  
AND CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) <= 31 
THEN 'M' 
WHEN CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) > 31 
AND CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) <= 93 
THEN 'Q'
WHEN CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) > 93 
AND CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) <= 183 
THEN 'S'
ELSE 'Y' 
END AS BlockDescription,
position.block,
CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS FLOAT) * 24.0  AS HoursInPeriod,
CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS INT) AS Days,
position.unit,
ISNULL(fee.pricediff,0) AS GBPMWh,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' 
THEN ((loadshapeprofile.he1 + loadshapeprofile.he2 + loadshapeprofile.he3 + loadshapeprofile.he4 + loadshapeprofile.he5 + loadshapeprofile.he6 + loadshapeprofile.he7 + loadshapeprofile.he8 + loadshapeprofile.he9 + loadshapeprofile.he10 + loadshapeprofile.he11 + loadshapeprofile.he12 + loadshapeprofile.he13 + loadshapeprofile.he14 + loadshapeprofile.he15 + loadshapeprofile.he16 + loadshapeprofile.he17 + loadshapeprofile.he18 + loadshapeprofile.he19 + loadshapeprofile.he20 + loadshapeprofile.he21 + loadshapeprofile.he22 + loadshapeprofile.he23 + loadshapeprofile.he24 + loadshapeprofile.he25) * 0.5)/24
ELSE  
((loadshapeprofile.he1 + loadshapeprofile.he2 + loadshapeprofile.he3  + loadshapeprofile.he4 + loadshapeprofile.he5 + loadshapeprofile.he6 + loadshapeprofile.he7 + loadshapeprofile.he8 + loadshapeprofile.he9 + loadshapeprofile.he10 + loadshapeprofile.he11 + loadshapeprofile.he12 + loadshapeprofile.he13 + loadshapeprofile.he14 + loadshapeprofile.he15 + loadshapeprofile.he16 + loadshapeprofile.he17 + loadshapeprofile.he18 + loadshapeprofile.he19 + loadshapeprofile.he20 + loadshapeprofile.he21  + loadshapeprofile.he22 + loadshapeprofile.he23 + loadshapeprofile.he24 + loadshapeprofile.he25) )/24
END AS MW,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' 
THEN 
((loadshapeprofile.he1 
+ loadshapeprofile.he2 
+ loadshapeprofile.he3 
+ loadshapeprofile.he4 
+ loadshapeprofile.he5
+ loadshapeprofile.he6
+ loadshapeprofile.he7
+ loadshapeprofile.he8
+ loadshapeprofile.he9
+ loadshapeprofile.he10
+ loadshapeprofile.he11
+ loadshapeprofile.he12
+ loadshapeprofile.he13
+ loadshapeprofile.he14
+ loadshapeprofile.he15
+ loadshapeprofile.he16 
+ loadshapeprofile.he17
+ loadshapeprofile.he18
+ loadshapeprofile.he19
+ loadshapeprofile.he20
+ loadshapeprofile.he21 
+ loadshapeprofile.he22
+ loadshapeprofile.he23
+ loadshapeprofile.he24
+ loadshapeprofile.he25) * 0.5) 
ELSE  
((loadshapeprofile.he1 
+ loadshapeprofile.he2 
+ loadshapeprofile.he3 
+ loadshapeprofile.he4 
+ loadshapeprofile.he5
+ loadshapeprofile.he6
+ loadshapeprofile.he7
+ loadshapeprofile.he8
+ loadshapeprofile.he9
+ loadshapeprofile.he10
+ loadshapeprofile.he11
+ loadshapeprofile.he12
+ loadshapeprofile.he13
+ loadshapeprofile.he14
+ loadshapeprofile.he15
+ loadshapeprofile.he16 
+ loadshapeprofile.he17
+ loadshapeprofile.he18
+ loadshapeprofile.he19
+ loadshapeprofile.he20
+ loadshapeprofile.he21 
+ loadshapeprofile.he22
+ loadshapeprofile.he23
+ loadshapeprofile.he24
+ loadshapeprofile.he25))
END AS MWh,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN 
((loadshapeprofile.he1 
+ loadshapeprofile.he2 
+ loadshapeprofile.he3 
+ loadshapeprofile.he4 
+ loadshapeprofile.he5
+ loadshapeprofile.he6
+ loadshapeprofile.he7
+ loadshapeprofile.he8
+ loadshapeprofile.he9
+ loadshapeprofile.he10
+ loadshapeprofile.he11
+ loadshapeprofile.he12
+ loadshapeprofile.he13
+ loadshapeprofile.he14
+ loadshapeprofile.he15
+ loadshapeprofile.he16 
+ loadshapeprofile.he17
+ loadshapeprofile.he18
+ loadshapeprofile.he19
+ loadshapeprofile.he20
+ loadshapeprofile.he21 
+ loadshapeprofile.he22
+ loadshapeprofile.he23
+ loadshapeprofile.he24
+ loadshapeprofile.he25) * 0.5)  * ISNULL(fee.pricediff,0)
ELSE  
((loadshapeprofile.he1 
+ loadshapeprofile.he2 
+ loadshapeprofile.he3 
+ loadshapeprofile.he4 
+ loadshapeprofile.he5
+ loadshapeprofile.he6
+ loadshapeprofile.he7
+ loadshapeprofile.he8
+ loadshapeprofile.he9
+ loadshapeprofile.he10
+ loadshapeprofile.he11
+ loadshapeprofile.he12
+ loadshapeprofile.he13
+ loadshapeprofile.he14
+ loadshapeprofile.he15
+ loadshapeprofile.he16 
+ loadshapeprofile.he17
+ loadshapeprofile.he18
+ loadshapeprofile.he19
+ loadshapeprofile.he20
+ loadshapeprofile.he21 
+ loadshapeprofile.he22
+ loadshapeprofile.he23
+ loadshapeprofile.he24
+ loadshapeprofile.he25) * ISNULL(fee.pricediff,0))
END AS BlockCost,
loadshapeprofile.he1,   
loadshapeprofile.he2,   
loadshapeprofile.he3,   
loadshapeprofile.he4,
loadshapeprofile.he5,   
loadshapeprofile.he6,   
loadshapeprofile.he7,   
loadshapeprofile.he8,   
loadshapeprofile.he9,   
loadshapeprofile.he10,  
loadshapeprofile.he11,  
loadshapeprofile.he12,  
loadshapeprofile.he13,  
loadshapeprofile.he14,  
loadshapeprofile.he15,  
loadshapeprofile.he16,  
loadshapeprofile.he17,  
loadshapeprofile.he18,  
loadshapeprofile.he19,  
loadshapeprofile.he20,  
loadshapeprofile.he21,  
loadshapeprofile.he22,  
loadshapeprofile.he23,  
loadshapeprofile.he24,  
loadshapeprofile.he25   
from Trade
inner join position on trade.trade = position.trade
inner join contract on position.contract = contract.contract
inner join loadshape on position.loadshape = loadshape.loadshape
inner join loadshapeprofile on loadshape.loadshape = loadshapeprofile.loadshape 
inner join fee on position.position = fee.dbvalue 
inner join feetimeperiod on fee.feetimeperiod = feetimeperiod.feetimeperiod and feetimeperiod.dbvalue = position.position
where contract.contract = '1111111'
and position.loadshape is not null
and fee.dbcolumn = 'POSITION' 
and fee.feemethod = 'COMMODITY PRICE'
and (CAST(DATEPART(MINUTE, feetimeperiod.begtime ) as decimal) = loadshapeprofile.offset)
and Trade.status = 'ACTIVE'
and trade.trade = 261333
and Trade.tradestatus IN ('FO Approval','TC Approval')
and feetimeperiod.feetimeperiod IN (select feetimeperiod from (select feetimeperiod, begtime, loadshapeprofile.offset, ROW_NUMBER() over (partition by  CONVERT(date,feetimeperiod.begtime), CONVERT(date,feetimeperiod.endtime),loadshapeprofile.offset order by CONVERT(date,feetimeperiod.begtime)) RowNumber from feetimeperiod) ftp where (ftp.RowNumber = 1 AND (CONVERT(date,ftp.begtime) =  CONVERT(date,loadshapeprofile.begtime) )) )
order by feetimeperiod.begtime

我的目标是为每个FeeTimePeriodBeginTime(日期(获得2行，一行偏移量为0，另一行偏移值为30，这实际上给了我一天的时间，但我真的很头疼(我这样做对吗？(

如有任何帮助，将不胜感激

您的问题不是因为row_number，而是因为您使用了带有"IN"条件的row_number。SQL Server正在删除具有"IN"条件的重复项。如果你想看到重复的行，你需要"加入"它。

最后我自己设法弄清楚了，我使用了一个通用的表表达式

;WITH FeePeriod_CTE 
AS
(
	select feetimeperiod.feetimeperiod, 
		feetimeperiod.begtime, 
		feetimeperiod.endtime, 
		loadshapeprofile.offset, 
		ROW_NUMBER() over (partition by CONVERT(date,feetimeperiod.begtime),CONVERT(date,feetimeperiod.endtime),loadshapeprofile.offset order by CONVERT(date,feetimeperiod.begtime)) RowNumber 
		from trade
		inner join position on trade.trade = position.trade
		inner join contract on position.contract = contract.contract
		inner join loadshape on position.loadshape = loadshape.loadshape
		inner join loadshapeprofile on loadshape.loadshape = loadshapeprofile.loadshape 
		inner join fee on position.position = fee.dbvalue 
		inner join feetimeperiod on fee.feetimeperiod = feetimeperiod.feetimeperiod and feetimeperiod.dbvalue = position.position
		where contract.contract = '110156'
		and position.loadshape is not null
		and fee.dbcolumn = 'POSITION' 
		and fee.feemethod = 'COMMODITY PRICE'
		and (CAST(DATEPART(MINUTE, feetimeperiod.begtime ) as decimal) = loadshapeprofile.offset)
		and Trade.status = 'ACTIVE'
		and trade.trade = 261333
		and Trade.tradestatus IN ('FO Approval','TC Approval')
		AND (CONVERT(date,feetimeperiod.begtime) =  CONVERT(date,loadshapeprofile.begtime) )
)

然后在WHERE子句中添加此行

and feetimeperiod.feetimeperiod IN (select feetimeperiod from FeePeriod_CTE where RowNumber = 1)

它现在完美地工作

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium