MySQL 复杂内连接

Question

假设股票有一个名为 TickerID 的列。我想用 equity.TickerID 替换 111。 MySQL 似乎无法解析范围并在我尝试时返回未知列。此 SQL 语句有效，但我需要为每个代码运行它。如果我能得到一张完整的 table 就太好了。

SELECT Ticker,
    IF(tbl_m200.MA200_Count = 200,tbl_m200.MA200,-1) AS MA200,
    IF(tbl_m50.MA50_Count = 50,tbl_m50.MA50,-1) AS MA50,
    IF(tbl_m20.MA20_Count = 20,tbl_m20.MA20,-1) AS MA20
FROM equity
INNER JOIN 
(SELECT  TickerID,AVG(Y.Close) AS MA200,COUNT(Y.Close) AS MA200_Count FROM 
(
    SELECT Close,TickerID FROM equity_pricehistory_daily
    WHERE TickerID = 111
    ORDER BY Timestamp DESC LIMIT 0,200
) AS Y
) AS tbl_m200
USING(TickerID)

INNER JOIN 
(SELECT  TickerID,AVG(Y.Close) AS MA50,COUNT(Y.Close)  AS MA50_Count FROM 
(
    SELECT Close,TickerID FROM equity_pricehistory_daily
    WHERE TickerID = 111
    ORDER BY Timestamp DESC LIMIT 50
) AS Y
) AS tbl_m50
USING(TickerID)

INNER JOIN 
(SELECT  TickerID,AVG(Y.Close) AS MA20,COUNT(Y.Close) AS MA20_Count FROM 
(
    SELECT Close,TickerID FROM equity_pricehistory_daily
    WHERE TickerID = 111
    ORDER BY Timestamp DESC LIMIT 0,20
) AS Y
) AS tbl_m20
USING(TickerID)

Answer 1

这似乎是 MySQL 的一些错误或“功能”。许多人似乎对外部表超出范围有同样的问题。

无论如何...您可以创建检索所需信息的函数:

DROP FUNCTION IF EXISTS AveragePriceHistory_20;

CREATE FUNCTION AveragePriceHistory_20(MyTickerID INT)
RETURNS DECIMAL(9,2) DETERMINISTIC
RETURN (
    SELECT AVG(Y.Close)
    FROM (
        SELECT Z.Close
        FROM equity_pricehistory_daily Z
        WHERE Z.TickerID = MyTickerID
        ORDER BY Timestamp DESC
        LIMIT 20
    ) Y
    HAVING COUNT(*) = 20
);

SELECT
    E.TickerID,
    E.Ticker,
    AveragePriceHistory_20(E.TickerID) AS MA20
FROM equity E;

您将得到 NULL 而不是 -1。如果这是不可取的，您可以使用 IFNULL(...,-1) 包装函数调用。

---

解决此问题的另一种方法是选择时间范围，而不是使用 LIMIT。

SELECT
    E.TickerID,
    E.Ticker,
    (
        SELECT AVG(Y.Close)
        FROM equity_pricehistory_daily Y
        WHERE Y.TickerID = E.TickerID
        AND Y.Timestamp > ADDDATE(CURRENT_TIMESTAMP, INTERVAL -20 DAY)
    ) AS MA20
FROM equity E;