在阅读之前请注意,我已经创建了一个查询来实现下面的功能,但是我只是想再次检查一下我是否做错了什么,或者是否有更有效的方法来做。
我在mysql表中有一组记录,如下所示(按run code snippet查看表)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=windows-1252"/>
<title></title>
<meta name="generator" content="LibreOffice 4.4.5.2 (Windows)"/>
<meta name="created" content="2018-06-21T09:33:19.600000000"/>
<meta name="changed" content="2018-06-21T09:42:20.756000000"/>
<style type="text/css">
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small }
</style>
</head>
<body>
<table cellspacing="0" border="0">
<colgroup width="85"></colgroup>
<colgroup width="84"></colgroup>
<colgroup width="110"></colgroup>
<colgroup width="127"></colgroup>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="left"><font color="#000000">Date</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">PortfolioId</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">ISIN</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">AssetOwnsership</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43269" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">18/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1234" sdnum="2057;"><font color="#000000">1234</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B069DV22</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1" sdnum="2057;"><font color="#000000">1</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B069DV22</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1.18" sdnum="2057;"><font color="#000000">1.18</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.1298" sdnum="2057;"><font color="#000000">0.1298</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000"><br></font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0" sdnum="2057;"><font color="#000000">0</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="82294" sdnum="2057;"><font color="#000000">82294</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000"><br></font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0" sdnum="2057;"><font color="#000000">0</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="82294" sdnum="2057;"><font color="#000000">82294</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000"><br></font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0" sdnum="2057;"><font color="#000000">0</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43270" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">19/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="82294" sdnum="2057;"><font color="#000000">82294</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.1378" sdnum="2057;"><font color="#000000">0.1378</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B069DV22</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1.25" sdnum="2057;"><font color="#000000">1.25</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.1175" sdnum="2057;"><font color="#000000">0.1175</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B6774691</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.1" sdnum="2057;"><font color="#000000">0.1</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="21" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1234" sdnum="2057;"><font color="#000000">1234</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.5" sdnum="2057;"><font color="#000000">0.5</font></td>
</tr>
</table>
<!--**************************************************************************-->
</body>
</html>
我想做的是找出AssetTownSership列今天和昨天的差异,根据两列(isin和portfolio id)的匹配显示当前头寸的增减,并输出今天的任何新记录/头寸。
基于上述数据集,输出应该是:(按RunCodeSnippet查看表)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=windows-1252"/>
<title></title>
<meta name="generator" content="LibreOffice 4.4.5.2 (Windows)"/>
<meta name="created" content="2018-06-21T10:02:42.397000000"/>
<meta name="changed" content="2018-06-21T10:03:10.865000000"/>
<style type="text/css">
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small }
</style>
</head>
<body>
<table cellspacing="0" border="0">
<colgroup span="4" width="85"></colgroup>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="32" align="left"><font face="Liberation Serif" color="#000000">Date</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">PortfolioId</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">ISIN</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">AssetOwnsership</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="32" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font face="Liberation Serif" color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font face="Liberation Serif" color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">GB00B069DV22</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.0700000000000001" sdnum="2057;"><font face="Liberation Serif" color="#000000">0.07</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="32" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font face="Liberation Serif" color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font face="Liberation Serif" color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="-0.0123" sdnum="2057;"><font face="Liberation Serif" color="#000000">-0.0123</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="32" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font face="Liberation Serif" color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="3521" sdnum="2057;"><font face="Liberation Serif" color="#000000">3521</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">GB00B6774691</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.1" sdnum="2057;"><font face="Liberation Serif" color="#000000">0.1</font></td>
</tr>
<tr>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="32" align="right" sdval="43271" sdnum="2057;0;DD/MM/YYYY"><font face="Liberation Serif" color="#000000">20/06/2018</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="1234" sdnum="2057;"><font face="Liberation Serif" color="#000000">1234</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="left"><font face="Liberation Serif" color="#000000">GB00B6774699</font></td>
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="right" sdval="0.5" sdnum="2057;"><font face="Liberation Serif" color="#000000">0.5</font></td>
</tr>
</table>
<!--**************************************************************************-->
</body>
</html>
我已经创建了下面的sql查询,它为我提供了这些数据:(假设今天的日期是20/06/2018来运行数据)
SELECT *, (case WHEN SUM(b.assetownsership-a.assetownsership)=0 then b.assetownsership
else SUM(b.assetownsership-a.assetownsership)
end) Difference, (case WHEN SUM(b.assetownsership-a.assetownsership)<0 then "S"
else "B"
end) BuySell
FROM performance.Assetallocation a
JOIN performance.Assetallocation b on b.isin = a.isin and b.portfolioID=a.portfolioID
where b.date>=DATE_FORMAT(CURDATE(), '%d/%m/%Y')-1
group by a.portfolioID, a.isin
我的问题是:
有没有更好的方法来实现这一点?
即使我的查询似乎提供了正确的输出,但我在查询中是否做错了什么?
2条答案
按热度按时间hsgswve41#
我认为您的查询需要有明显的改变,因为您需要根据protfolioid和isin找到今天和昨天的区别,应该是这样的
8fsztsew2#
我认为这是一种更好的查询方式:
笔记:
它使用条件聚合,因此
join
是必要的。这是假设
date
真是个约会。如果它有时间成分,则使用date()
函数或类似逻辑。世界上唯一的未聚合表达式
select
应该是group by
. 因此,没有select *
.进行日期/时间比较时,请使用本机字符串操作。不要把日期/时间转换成字符串,除非你真的需要。