根据其中的值将字段添加到临时表的最佳方法 [英] Best way to add fields to a temp table based on values within it

问题描述

你好，我希望你很好。

我一直在忙着构建一个存储过程，将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]

  ; ，[日期时间]

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

{

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

< 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（由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

推荐答案

Hi TilleyTech Ltd，

Hi TilleyTech Ltd,

根据您的描述，我很抱歉我无法理解您的要求。

Per your description, I am sorry that I could not understand your requirement clearly.

 

请与我们分享您的
原始 表结构 （ArtxFieldsToDecode，ArtxfeedXmlInfo等） 以及一些示例数据以及您的预期结果？这样我们就可以获得正确的
方向并进行一些测试。

 

如果原始逻辑很复杂，请分享一个简单的例子。

If your original logic is complex, please share us a simple example.

 

最好的问候，

Rachel

Rachel

这篇关于根据其中的值将字段添加到临时表的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！