mysql - MySQL复杂内部联接

假设权益有一个名为TickerID的列。我想用Equity.TickerID替换111。当我尝试这样做时，MySQL似乎无法解析范围并返回未知列。该SQL语句有效，但我需要为每个股票运行它。如果能得到一张完整的桌子会很好。

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)

最佳答案

这似乎是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;

tickerID

mysql - MySQL复杂内部联接