根据其中的值将字段添加到临时表的最佳方法

你好，我希望你很好。

我一直在忙着构建一个存储过程，将XML中的数据转换为字段/值对。

I've been busy building a stored procedure that transforms data from XML into field / value pairs.

我最近发现我必须添加一个MID计算，从2个不同的行派生，所以我添加了这个，下一部分是根据这个MID添加12个计算行....（ !!）

I recently found out that I had to add a MID calculation, derived from 2 different rows, so I have added this and the next part is to add 12 calculated rows based on this MID....(!!)

我在中间添加了一些我需要建议的位。它非常完整，所以提前感谢您的帮助！

I have added the bit in the middle that I will need advice on. It's quite full on so thanks in advance for your assistance!

创建程序dbo。[ sp_ArtxTickerRealTimeMarketdataXMLFieldSplitter]

AS

BEGIN



DECLARE @lower VARCHAR（24）

DECLARE @upper VARCHAR（24）

DECLARE @ms VARCHAR（4）

DECLARE @secondsForDateRange INT

DECLARE @fieldToAlias VARCHAR（50）

DECLARE @SourceToCheck VARCHAR（50）

CREATE PROCEDURE dbo.[sp_ArtxTickerRealTimeMarketdataXMLFieldSplitter]
AS
BEGIN

DECLARE @lower VARCHAR(24)
DECLARE @upper VARCHAR(24)
DECLARE @ms VARCHAR(4)
DECLARE @secondsForDateRange INT
DECLARE @fieldToAlias VARCHAR(50)
DECLARE @SourceToCheck VARCHAR(50)

DROP TABLE IF EXISTS #tempY yesterday;

DROP TABLE IF EXISTS #tempFields;

DROP TABLE IF EXISTS #aliases;
$


SELECT @secondsForDateRange = -15

SELECT @ms ='。000 '¥
SELECT @fieldToAlias ='FIELD_1'

SELECT @SourceToCheck ='SYSTEM1'
$


--TO做 - 在未来我们可能想要使这个位置/低于一般的通用但是在一定程度上b
- 设置上面的一个字段。我们通过这个表来查看所有字段，而不是一个字$
- 所有这些都在TEMP / MEM表中更快了
SELECT [name]，别名INTO＃别名来自ArtxFieldsToDecode WHERE [name] = @fieldToAlias



- 获取包含XML列的表格中的最大日期时间，坐在它后面1分钟

- - 落后1分钟，尝试30秒

SELECT @lower = FORMAT（DATEADD（第二，@ secondsForDateRange，MAX（[DATETIME]）），'dd MMM yyyy HH：mm：ss'）+ @ms，@ up = FORMAT（MAX（[DATETIME]），'dd MMM yyyy HH：mm：ss'）  +'。000'+ @ms

来自ArtxfeedXmlInfo（NOLOCK）



--XML - > FID /价值转换

; WITH rs AS

（

SELECT ISIN，

CHARINDEX时的情况（@ SourceToCheck，col.value（'（source）[1]'，'VARCHAR（50）'））> 0 THEN

案例PARSENAME（REPLACE（col.value（'（id）[1]'，'VARCHAR（50）'），'（'，'。' ），2）= @fieldToAlias然后
（SELECT别名FROM #aliases WHERE [Name] = PARSENAME（REPLACE（col.value） （'（id）[1]'，'VARCHAR（50）'），'（'，'。'），2））

ELSE

PARSENAME（REPLACE（col.value（'（id）[1]'，'VARCHAR（50） ）'），'（'，'。'），2）

结束

ELSE 

PARSENAME（REPLACE（col.value（'（ id）[1]'，'VARCHAR（50）'），'（'，'。'），2） 

  END AS fid

     ，col.value（'（field）[1]'，'VARCHAR（50）'）AS [value]

&nbsp ; ，[日期时间]

FROM（选择ISIN，日期时间，[数据] .query（'< root>

{

  for / ARTXPriceFeedRecord / field $ field $
 返回<行>

< id> {data （$ field / @ ID）}< / id>

< field> {data（$ field）}< / field>

< source> {data（ARTXPriceFeedRecord / @ Source）}< / source>

< / row>

}&lt ; / root>'）AS dataXML

FROM ArtxfeedXmlInfo）tbl

CROSS APPLY tbl.dataXML。 nodes（'/ root / row'）AS tab（col）

WHERE [Datetime] BETWEEN @lower AND @upper）

，noDuplicates AS

（

SELECT rs。*

  ，rn = ROW_NUMBER（）OVER（由ISIN分割，fid ORDER BY [Datetime] DESC）

FROM rs

）



- 没有转换为临时表的转换为
SELECT ISIN，FID，[Value]，[Datetime] INTO＃tempFields  

FROM noDuplicates

WHERE rn = 1;



- ADD计算字段

DROP TABLE IF EXISTS #tempYesterday;
DROP TABLE IF EXISTS #tempFields;
DROP TABLE IF EXISTS #aliases;

SELECT @secondsForDateRange = -15
SELECT @ms = '.000'
SELECT @fieldToAlias = 'FIELD_1'
SELECT @SourceToCheck = 'SYSTEM1'

--TO DO - IN FUTURE WE MAY WANT TO MAKE THIS BIT/BELOW ENTIRELY GENERIC BUT AT MOMENT
--WE SET THE ONE FIELD ABOVE. WE'D LOOK THROUGH THIS TABLE OF ALL FIELDS RATHER THAN ONE
--BRING THEM ALL IN AS FASTER IN TEMP / MEM TABLE
SELECT [name], alias INTO #aliases FROM ArtxFieldsToDecode WHERE [name] = @fieldToAlias

--Get max datetime in table containing XML column the sit 1min behind it
--Was 1 min behind, try 30 seconds
SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
FROM ArtxfeedXmlInfo (NOLOCK)

--XML -> FID / Value transform
;WITH rs AS
(
SELECT ISIN,
CASE WHEN CHARINDEX(@SourceToCheck,col.value('(source)[1]','VARCHAR(50)') ) > 0 THEN
CASE WHEN PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) = @fieldToAlias THEN
(SELECT alias FROM #aliases WHERE [Name] = PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2))
ELSE
PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2)
END
ELSE 
PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) 
  END AS fid
      , col.value('(field)[1]','VARCHAR(50)') AS [value]
  , [Datetime]
FROM (SELECT ISIN, Datetime, [data].query('<root>
{
  for $field in /ARTXPriceFeedRecord/field
  return <row>
<id>{data($field/@ID)}</id>
<field>{data($field)}</field>
<source>{data(ARTXPriceFeedRecord/@Source)}</source>
</row>
}</root>') AS dataXML
FROM ArtxfeedXmlInfo) tbl
CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
WHERE [Datetime] BETWEEN @lower AND @upper)
, noDuplicates AS
(
SELECT rs.*
  , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fid ORDER BY [Datetime] DESC)
FROM rs
)

--Place transformed no dupes into temp table
SELECT ISIN, FID, [Value], [Datetime] INTO #tempFields  
FROM noDuplicates
WHERE rn = 1;

--ADD CALCULATED FIELDS

- 添加MID_SPREAD

--Add MID_SPREAD

INSERT INTO #tempFields

SELECT DISTINCT ISIN， 

'MID_SPREAD'AS FID，

AVG（CAST（值为FLOAT））AS [Value]，

GetDate（）AS Now

FROM #tempFields

WHERE FID IN（'ASK_SPREAD'，'BID_SPREAD'）

GROUP BY ISIN

INSERT INTO #tempFields
SELECT DISTINCT ISIN, 
'MID_SPREAD' AS FID,
AVG(CAST(VALUE AS FLOAT)) AS [Value],
GetDate() AS Now
FROM #tempFields
WHERE FID IN('ASK_SPREAD', 'BID_SPREAD')
GROUP BY ISIN

- TODO根据此MID添加12个新的计算字段

--TODO ADD 12 NEW CALCULATED FIELDS BASED ON THIS MID

- 我需要建议

我们将使用不同的这些列中的一列与我们刚刚派生的MID_SPREAD一起，根据#tempFields中的ISIN，它取决于称为'SEG_TEXT'的FID的值（例如， CASE WHEN FID = 'SEG_TEXT' AND VALUE = 'VALUE_1' THEN MID_SPREAD - YESTERDAY_CLOSE_AVG

We will use a different one of these columns in conjunction with the MID_SPREAD we just derived, per ISIN in #tempFields, and it depends on the value of FID called 'SEG_TEXT' (e.g. CASE WHEN FID='SEG_TEXT' AND VALUE = 'VALUE_1' THEN MID_SPREAD - YESTERDAY_CLOSE_AVG

目标是插入新行回#tempFields，如我为MID_SPREAD，基于所述数据从#tempYesterday（也将有#tempLastWeek和#tempLastMonthEnd太

Goal is to insert a new row back into #tempFields, like i did for MID_SPREAD, based on the data from #tempYesterday (there will also be #tempLastWeek and #tempLastMonthEnd too!

-------------------------------

-------------------------------

- 合并将首先添加然后更新仅添加如果新增加
MERGE marketdatatest mdt

USING #tempFields tf

ON mdt.isin = tf.isin

AND mdt.fid = tf.fid

当匹配时为
更新>
SET mdt.value = tf.value，mdt.timestamp = tf。[datetime]

当没有与目标匹配时，那么
INSERT（isin，fid） ，价值，时间戳）

VALUES（tf.isin，tf.fid，tf.value，tf.Datetime）;



END

GO

--Merge will add first then update only adding if new
MERGE marketdatatest mdt
USING #tempFields tf
ON mdt.isin = tf.isin
AND mdt.fid = tf.fid
WHEN MATCHED THEN
UPDATE
SET mdt.value = tf.value, mdt.timestamp = tf.[datetime]
WHEN NOT MATCHED BY TARGET THEN
INSERT (isin, fid, value, timestamp)
VALUES (tf.isin, tf.fid, tf.value, tf.Datetime);

END
GO