我有一个查询,该查询用于获取各种输入来计算资产排名。为了获得各种值以根据用户输入来计算资产等级,我正在对一个表使用多个子查询。但是,这花费了太多时间。谁能帮我即兴查询吗?
SELECT AssetId,
AssetName,
Isin,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 12 MONTH
ORDER BY DDate ASC LIMIT 1) AS rafval,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 12 MONTH
ORDER BY DDate DESC LIMIT 1) AS ralval,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 3 MONTH
ORDER BY DDate ASC LIMIT 1) AS rbfval,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 3 MONTH
ORDER BY DDate DESC LIMIT 1) AS rblval,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate ASC LIMIT 1) AS rcfval,
(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate DESC LIMIT 1) AS rclval,
(SELECT STD(DClose)
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate DESC LIMIT 1) AS vstd
FROM assets a
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
AND a.IsActive=1;
在上面的查询中,子查询中提到的所有间隔都是变量。它们来自用户输入。包含1000个资产的资产表,并且eod_data将包含数百万条记录。
我还为where子句中涉及的所有字段创建了索引。
表结构资产:AssetId,AssetName,Isin,IsActive | eod_data:Isin,DClose,DDate
上面的查询耗时将近11分钟。
提前致谢。
请在此处找到示例数据库http://sqlfiddle.com/#!9/d0a50/3
这是我的下一个尝试。我已经测试了一些不同的加入。这是最快的方法(快1400倍)。STD()列暂时未实现。您能检查一下其他输出是否正确吗
..以及带有STD()的决赛(我希望如此)。
SELECT
a.AssetId
, a.AssetName
, a.Isin
, CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
, CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
, CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
, CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
, CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
, CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
, COALESCE(ed.vstd,0) AS vstd
FROM (
SELECT
ed.Isin
, MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
, MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
, MIN(IF( DDate >= now()-INTERVAL 3 MONTH, EodDataId, NULL)) AS id_rbfval
, MAX(IF( DDate >= now()-INTERVAL 3 MONTH, EodDataId, NULL)) AS id_rblval
, MIN(IF( DDate >= now()-INTERVAL 40 DAY , EodDataId, NULL)) AS id_rcfval
, MAX(IF( DDate >= now()-INTERVAL 40 DAY , EodDataId, NULL)) AS id_rclval
, std(IF( DDate >= now()-INTERVAL 40 DAY , NULL, DClose )) AS vstd
FROM eod_data ed
WHERE ed.DDate >= now()-INTERVAL 12 MONTH
GROUP BY ed.Isin
ORDER BY ed.EodDataId ASC
) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
AND a.IsActive=1
ORDER BY a.AssetId;
SELECT
a.AssetId
, a.AssetName
, a.Isin
, CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
, CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
, CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
, CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
, CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
, CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
FROM (
SELECT
ed.Isin
, MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
, MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
, MIN(IF( DDate >= now()-INTERVAL 3 MONTH, EodDataId, NULL)) AS id_rbfval
, MAX(IF( DDate >= now()-INTERVAL 3 MONTH, EodDataId, NULL)) AS id_rblval
, MIN(IF( DDate >= now()-INTERVAL 20 DAY , EodDataId, NULL)) AS id_rcfval
, MAX(IF( DDate >= now()-INTERVAL 20 DAY , EodDataId, NULL)) AS id_rclval
FROM eod_data ed
WHERE ed.DDate >= now()-INTERVAL 12 MONTH
GROUP BY ed.Isin
ORDER BY ed.DDate ASC
) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
AND a.IsActive=1
ORDER BY a.AssetId;
您可以再测试一次吗?
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句