主页 / user-136322

V_immo's questions

Martin Hope
V_immo
Asked: 2019-10-30 07:31:11 +0800 CST
  • 0

我正在编写一个 SQL 查询来复制下面的 Excel 结果。

在 AI 列中，从 2018 年 10 月 30 日到 2019 年 10 月 29 日，按 ASC 顺序有 252 个值在 BV 列中，我有各自的 PL 编号，我想计算 TestPL 列，其值为忽略前 3 个 PL 值，第 4 个单元格将添加B2:B5，第 5 个单元格将是 B3:B6，依此类推，对于所有 251 个值

TestPL 应该从第 4 行开始，并且应该有单元格 B2:B5 的总和，第 5 行将是 B3:B6，依此类推，直到第 252 行，即 B249:B252

在此处输入图像描述

sql-server excel
Martin Hope
V_immo
Asked: 2019-09-05 07:13:13 +0800 CST
  • 0

我正在尝试将每个交易者的前 253 个值从临时交易者表插入到临时结果表中。

我正在使用以下代码来做到这一点。

CREATE TABLE #trad ( Trad varchar(50))    
IF @trad is not null    
INSERT INTO #Trad    
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas          
ELSE INSERT INTO #Traders    
Select distinct Trader from TraderMap tm     
where traderorgroup = 'trader'  


CREATE TABLE #result      
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,      
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,      
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt 
INT,Trad varchar(100))  

DECLARE @traderlist AS Varchar(100)      
DECLARE traderlist CURSOR FOR      
SELECT DISTINCT Traders FROM #traders      
OPEN traderlist      
FETCH next FROM traderlist INTO @traderlist      
WHILE @@FETCH_STATUS = 0      
BEGIN  
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, 
dCnt,Trad)      
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P 
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,      
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC) 
dCnt,pl.TradName      
FROM Trad_PL pl INNER JOIN rep_tab var      
ON pl.Portdate = var.V_Date      
AND pl.Port = var.Port     
AND pl.TradName = var.Trad      
INNER JOIN Trad_Guid gui      
ON pl.Port = gui.Port     
AND pl.TradName = gui.Trad      
WHERE pl.Portdate <= @portdate      
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE 
@port END      
AND pl.ONA = 'Act'  
AND pl.TradName = @tradlist          
AND var.PType = '0Trad' 
group by  
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist      
END      
CLOSE tradlist      
DEALLOCATE tradlist

此代码运行数小时。有什么有效的方法可以从 Trad_PL 和 rep_tab 中获取每个交易的前 253 行。

sql-server cursors
Martin Hope
V_immo
Asked: 2019-06-13 08:12:38 +0800 CST
  • 0

我正在尝试在 Excel 中复制 Small 函数，它是 Small(Array,K) 从数据流中给出第 K 个最小元素。

我想找到特定列值的第 13 个最小值。在我的表中，对于每个 A 列行值，我有 2 列 A 和 B，我可以说 20 B 值。我正在尝试从 B 列中找到每个 A 值的第 13 个最小值

我正在使用以下查询从 group by 子句中获取 min

select A,min(B) from test group by Level_Name

我想找到第 13 个最小值而不是最小值。

我添加了一个新的查询作为

with a as(
select A,B,ROW_NUMBER() over (order by A,B) as rownum 
 from test 
group by Level_Name,pl
)
select A,B from a 
where rownum=13
group by A,B

它仅返回 A 值的 1 的第 13 个数字，但 A 中有多行。

sql-server
Martin Hope
V_immo
Asked: 2017-11-17 12:36:16 +0800 CST
  • 2

我有以下代码开发 2 个 XML 文件，但我希望它们位于一个具有多个组织标签的文件中。

SELECT 
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

SELECT 
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from
(
    Select 'Coll' AS 'orgRole'
    UNION ALL 
    Select 'Lega' AS 'orgRole'
) a
FOR XML PATH(''), TYPE, ELEMENTS
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

当我运行它时，它会生成 2 个 XML 文件作为输出，如下所示。

<collection>
 <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
  </externalCodes>
</organisation>

和

<collection>
 <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

虽然我想将它添加到单个文件下以将结果作为 Collection 作为 root 并将这些文件合并到组织标记中，如下所示。

 <collection>
  <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
   </externalCodes>
  </organisation>
  <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

如果需要任何其他信息，请在下方评论。谢谢。

sql-server-2008 xml
Martin Hope
V_immo
Asked: 2017-11-16 13:19:25 +0800 CST
  • 3

我必须使用 sql 创建一个 XML 文件

 <externalCodes>
  <externalCode>
   <system>CALYPSO</system>
   <extCode>cptname</extCode>
  </externalCode>
 <externalCode>
   <system>CMS-GDP</system>
   <extCode>cptname</extCode>
 </externalCode>
 <externalCode>
   <system>Manual</system>
   <extCode>cptname</extCode>
 </externalCode>

我的SQL如下

(SELECT system, extCode from(Select 'CALYPSO' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') a
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from(Select 'CMS-GDP' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') b
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from( Select 'MANUAL' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') c
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode"

我的输出如下：

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </externalCodes>

但预期的输出如下

   <externalCodes>
     <externalCode>
       <system>CALYPSO</system>
       <extCode>Mon</extCode>
     </externalCode>
    <externalCode>
       <system>CMS-GDP</system>
       <extCode>Mon</extCode>
     </externalCode>
     <externalCode>
       <system>MANUAL</system>
       <extCode>Mon</extCode>
     </externalCode>
    </externalCodes>
sql-server xml
Martin Hope
V_immo
Asked: 2017-10-11 07:13:38 +0800 CST
  • 0

存储过程

CREATE     procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)    
as    

Declare @intdate datetime    
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'    
and rate_date <= @portdate    

Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,    
NonDiscountNew = null, DiscountNew = null    
where    
port = @port and portdate = @portdate    

Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *    
      dbo.Foo(ccd, 'USD', @portdate,    
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))     
end),    
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)    
from    
Phy p1    
where    
port = @port and portdate = @portdate    
and    
tr.trans = p1.trans    
and    
p1.Sub <> 'Option'    


Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *    
      dbo.Foo(ccd, 'USD', @portdate,     
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),    
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)    
from    
Phy p1    
where    
port = @port and portfolio = @portfolio    
and    
tr.trans = p1.trans    
and    
p1.Sub = 'Option'





CREATE   function [dbo].[Foo]  
(@currency1 varchar(10),   
@currency2 varchar(10),  
@portdate datetime, @month int) returns float  

As  
BEGIN  
Declare  
@CurrentRate float,  
@Ratedate datetime  


select @Ratedate = max(rate_date) from fx where  
(  
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month  
or  
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month  
)  
and  
@portdate >= Rate_date  


IF exists ( select * from fx where currency1 = @currency1  
and currency2 = @currency2  and rate_date = @Ratedate and forward_month = @month)   


  SELECT @CurrentRate =  Rate from fx where currency1 = @currency1 and  
  currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month  



ELSE   



  IF exists ( select * from fx where currency1 =  
  @currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)  


  select @CurrentRate = 1/Rate  from fx  where currency1 = @currency2  
  and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month  


  ELSE  
   select @CurrentRate = 1   


return (@CurrRate )  
END

我知道标量函数调用会限制查询的性能。有人可以看到一种方法来删除函数调用，用JOINorCROSS APPLY或 something 代替它们，看看这是否有助于提高性能？我试过了，但没能找到办法。

有关当前性能的更多信息：单次运行该过程，更新约 25,000 行，运行需要 20-25 分钟。查看执行计划，我看到函数中有很多读取，我认为使用JOIN解决方案（或类似解决方案），读取数量会急剧下降，性能可能会提高。另外，如上所述，我听说标量函数调用（通常）在查询中“不好”。

sql-server performance