MySQL 使用临时表(学习)

Question

我目前正在学习 SQL，以便从我们的 Magento 数据库制作一个简单的 RFM 报告，我目前可以通过导出两个查询并将它们粘贴到 Excel 模板中来完成此操作，我想摆脱 Excel 模板。

我认为创建一个临时表来保存过去 6 个月的数据可以帮助了解购买模式是否发生变化(向客户表明我们正在流失)。

     CREATE TEMPORARY TABLE Months_RFM_6
     SELECT
     customer_email,
     count(*) AS Order_Count,
     MAX(created_at) as Last_Order_Date,
     MIN(created_at) as First_Order_Date,
     SUM(base_total_paid) AS Total_Lifetime_Sales,
     (SUM(base_total_paid) / count(*)) AS AOV,
     SUM(base_total_offline_refunded + base_total_online_refunded) AS Refund_Total,
     AVG(total_qty_ordered) AS UPO
     FROM
     mage_sales_order AS o
     WHERE
     o.created_at > DATE_SUB(now(), INTERVAL 6 MONTH)
    GROUP BY
    customer_email
    ORDER BY
    count(*) DESC,

    SELECT
    customer_email,
    count(*) AS Order_Count,
    MAX(created_at) as Last_Order_Date,
    MIN(created_at) as First_Order_Date,
    SUM(base_total_paid) AS Total_Lifetime_Sales,
    SUM(base_total_paid) / count(*)) AS AOV,
    SUM(base_total_offline_refunded + base_total_online_refunded) AS Refund_Total,
    AVG(total_qty_ordered) AS UPO
    FROM
    mage_sales_order AS o
    GROUP BY
    customer_email
    ORDER BY
    count(*) DESC

我确定这是临时表和 SQL 查询之间的语法问题。在我可以让它工作之前，我不会将临时表添加到查询中，谢谢。

Answer 1

创建临时表后，内容基于 select 中使用的选择结果，因此您应该只使用临时表中的 select * 来查看结果，例如:

CREATE TEMPORARY TABLE Months_RFM_6
SELECT customer_email,
count(*) AS Order_Count,
MAX(created_at) as Last_Order_Date,
MIN(created_at) as First_Order_Date,
SUM(base_total_paid) AS Total_Lifetime_Sales,
(SUM(base_total_paid) / count(*)) AS AOV,
SUM(base_total_offline_refunded + base_total_online_refunded) AS Refund_Total,
AVG(total_qty_ordered) AS UPO
FROM mage_sales_order 
WHERE mage_sales_order.created_at > DATE_SUB(now(), INTERVAL 6 MONTH)
GROUP BY customer_email
ORDER BY  Order_Count DESC
;

select * from Months_RFM_6