mysql基于两列查找记录与前一天的差异

svmlkihl  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(303)

在阅读之前请注意，我已经创建了一个查询来实现下面的功能，但是我只是想再次检查一下我是否做错了什么，或者是否有更有效的方法来做。
我在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

我的问题是：
有没有更好的方法来实现这一点？
即使我的查询似乎提供了正确的输出，但我在查询中是否做错了什么？

sqlmysqlJoingroup-by

2条答案

按热度按时间
hsgswve4

hsgswve41#

我认为您的查询需要有明显的改变，因为您需要根据protfolioid和isin找到今天和昨天的区别，应该是这样的

SELECT 
    b.date, 
    (CASE WHEN (b.assetownsership-a.assetownsership)<=0 THEN b.assetownsership
        ELSE (b.assetownsership-a.assetownsership) END) Difference, 
    (CASE WHEN (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') 
        AND a.date = DATE_FORMAT(CURDATE(), '%d/%m/%Y')-1
赞(0）分享  回复(0）举报  2021-06-20
8fsztsew

8fsztsew2#

我认为这是一种更好的查询方式：

select a.portfolioID, a.isin,
       sum(case when a.date = curdate()
                then a.assetownsership
                when a.date = curdate() - interval 1 day 
                then - a.assetownsership
           end) as difference,
       (case when sum(case when a.date = curdate()
                           then a.assetownsership
                           when a.date = curdate() - interval 1 day 
                           then - a.assetownsership
                      end) < 0
              then 'S' else 'B'
         end) as BuySell
from performance.Assetallocation a
where a.date >= curdate() - interval 1 day
group by a.portfolioID, a.isin;

笔记：
它使用条件聚合，因此 join 是必要的。
这是假设 date 真是个约会。如果它有时间成分，则使用 date() 函数或类似逻辑。
世界上唯一的未聚合表达式 select 应该是 group by . 因此，没有 select * .
进行日期/时间比较时，请使用本机字符串操作。不要把日期/时间转换成字符串，除非你真的需要。

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com