mysql - 将源表中的选择查询结果插入派生表

Question

我的问题是填充导出的每日表(源自源表prices_daily)中的最后两列。

(源表)prices_daily:

sequence        INT(11)         NO    PRI  AUTO_INCREMENT
symbol          VARCHAR(6)      NO    MUL
date            DATE            NO    MUL
high            DECIMAL(8,2)    YES
low             DECIMAL(8,2)    YES
close_adj       DECIMAL(8,2)    YES

(目标表)衍生_每日:

symbol           VARCHAR(6)     YES
date             DATE           NO
mov_avg10        DECIMAL(8,2)   YES
std_dev10        DECIMAL(8,2)   YES
range_daily      DECIMAL(8,2)   YES
range_std_dev30  DECIMAL(8,2)   YES

我可以使用以下代码填充前 4 列:

INSERT INTO derived_daily(symbol, date, mov_avg10, std_dev10)
(
SELECT t1.symbol, t1.date, AVG(t2.close_adj) AS mov_avg10, STDDEV(t2.close_adj) 
           AS std_dev10 
FROM prices_daily t1 LEFT OUTER JOIN prices_daily t2 
    ON t2.symbol = t1.symbol AND (t1.sequence - t2.sequence)BETWEEN 0 AND 9 
    WHERE t1.symbol = 'C' GROUP BY t1.date
) ; 

但是当我尝试用以下内容填充派生表中的“range_daily”时:

INSERT INTO derived_daily(symbol, date, range_daily)
(
SELECT t2.symbol, t2.date, (high - low) AS range_daily 
FROM prices_daily t1 
LEFT OUTER JOIN derived_daily t2 
    ON t2.symbol = t1.symbol AND t2.date = t1.date 
    WHERE t2.symbol = 'C' 
    ORDER BY t2.date
) ; 

它将其放置在正确的列中，但位于表底部的新行中，而不是包含丢失数据的现有行(range_daily 和最终的 range_std_dev30)。我已经尝试了一些调整，主要是“错误 1364，字段“日期”没有默认值。我想填充最后两列以与我已经放置在表中的内容相匹配(相同行)，而不是底部的新行。

我花了很长时间查看相关问题/答案，但仍然无法与我的问题相关(菜鸟...抱歉)。任何帮助/建议/等。将不胜感激!附:正在努力适本地格式化问题，但必须来到主要位置(该网站位于顶部)，因为我感到沮丧，哈哈。

谢谢，汤姆

Answer 1

我认为您想要更新现有行，而不是插入新行。

例如:

UPDATE ( SELECT t1.symbol
              , t1.date
              , AVG(t2.close_adj) AS mov_avg10
              , STDDEV(t2.close_adj) AS std_dev10
           FROM prices_daily t1
           LEFT
           JOIN prices_daily t2
             ON t2.symbol = t1.symbol
            AND (t1.sequence - t2.sequence) BETWEEN 0 AND 9
          WHERE t1.symbol = 'C'
          GROUP
             BY t1.symbol
              , t1.date
       ) s
  JOIN derived_daily t
    ON t.symbol = s.symbol
   AND t.date = s.date
   SET t.mov_avg10 = s.mov_avg10
     , t.std_dev10 = s.std_dev10

为了进行测试，您可以将 UPDATE 关键字替换为 SELECT t.symbol, t.date, t.mov_avg10, t.std_dev10, s.mov_avg10, s.std_dev10 FROM 并删除末尾的 SET 子句。这将列出 t 中将要更新的行、行中的现有值以及将分配的新值。

<小时/>

跟进

要获取插入的所有列，您可以组合三个查询。这是一个巨大的查询，但是一旦我们将其分解为各个组成部分，它就有意义了。

INSERT INTO derived_daily
( symbol
, `date`
, mov_avg10
, std_dev10
, range_daily
, range_std_dev30
)
SELECT q.symbol
     , q.date
     , q.mov_avg10
     , q.std_dev10
     , r.range_daily
     , s.range_std_dev30
  FROM ( SELECT t1.symbol
              , t1.date
              , AVG(t2.close_adj) AS mov_avg10
              , STDDEV(t2.close_adj) AS std_dev10
           FROM prices_daily t1
           LEFT
           JOIN prices_daily t2
             ON t2.symbol = t1.symbol
            AND (t1.sequence - t2.sequence) BETWEEN 0 AND 9
          WHERE t1.symbol = 'C'
          GROUP BY t1.symbol, t1.date
       ) q
  LEFT
  JOIN ( SELECT t3.symbol
              , t3.date
              , (t3.high - t3.low) AS range_daily
           FROM prices_daily t3
          WHERE t3.symbol = 'C'
       -- GROUP BY t3.symbol, t3.date
       ) r
    ON r.symbol = q.symbol
   AND r.date = q.date
  LEFT
  JOIN ( SELECT t4.symbol
              , t4.date
              , STDDEV(d.range_daily) AS range_std_dev30
           FROM prices_daily t4
           LEFT
           JOIN ( SELECT t5.symbol
                       , t5.date
                       , (t5.high - t5.low) AS range_daily
                    FROM prices_daily t5
                   WHERE t5.symbol = 'C'
                -- GROUP BY t5.symbol, t5.date
                ) d
             ON d.symbol = t4.symbol
            AND d.date >= t4.date
            AND d.date <= t4.date + INTERVAL -30 DAY
          WHERE t4.symbol = 'C'
          GROUP BY t4.symbol, t4.date
       ) s
    ON s.symbol = q.symbol
   AND s.date = q.date
 ORDER BY q.date, q.symbol

<小时/>

该查询本质上是这样的形式

INSERT INTO target
SELECT q.key
     , q.val
     , r.val
     , s.val
  FROM q
  LEFT
  JOIN r ON r.key = q.key
  LEFT
  JOIN s ON s.key = q.key

这假设 (symbol,date) 在(源)prices_daily 表上是唯一的，并且在(目标)衍生_daily< 上也是唯一的 表。

为了进行测试，我们可以运行分别提供 q、r 和 s 值的查询。

我尝试了 range_std_dev30...STDDEV 的公式，计算从当前日期到 30 天的范围值。

(我对使用 AUTO_INCRMENT 序列来派生过去 10 行感到有点困扰......我们通常不依赖于保证 auto_increment id 值将是连续且没有间隙的。这是我们观察到的，但不能保证行为，特别是在以不同的顺序插入行的情况下。

(对于这 30 天，我使用了日期范围比较。)