php - 多个 SubSelect 内部连接 ​​Mysql

Question

我正在尝试在一个查询中创建多个 Z 分数 [(value-AVG)/STD] 并过滤它们。

到目前为止，我想到了这个；然而，在你看它之前，有一些法语词，“quartier”就像一个地区/自治市镇，“prix”是一个价格。

SELECT ((t1.prix-A1.HMEAN)*100/A1.STD_SAMPLE) AS Z,
q.myId AS q_myId
FROM immobilier_ad_blank AS t1 
LEFT JOIN Adresse AS a ON t1.adresse_id=a.id
LEFT JOIN Quartier AS q ON a.quartier_id=q.id
CROSS JOIN (
    SELECT AVG(t2.prix) AS HMEAN, 
    STD(t2.prix) AS STD_SAMPLE,
    q.myId AS quartier_myId
    FROM immobilier_ad_blank AS t2
    LEFT JOIN Adresse AS a ON t2.adresse_id=a.id
    LEFT JOIN Quartier AS q ON a.quartier_id=q.id
    GROUP BY quartier_myId
) AS A1 ON q.myId = A1.quartier_myId

它似乎有效，但我不确定我这样做是否正确，我只添加了一个过滤条件，但我将添加多达 6 个条件，到目前为止，这种方法看起来非常麻烦。

最终看起来像这样

SELECT 
((t1.prix-A1.HMEAN)*100/A1.STD_SAMPLE) AS Z1,
((t1.prix-A2.HMEAN)*100/A1.STD_SAMPLE) AS Z2,
((t1.prix-AN.HMEAN)*100/A1.STD_SAMPLE) AS ZN,
All Relevant selects
FROM immobilier_ad_blank AS t1 
All Relevant LEFT Joins
CROSS JOIN (
    SELECT AVG(t2.prix) AS HMEAN, 
    STD(t2.prix) AS STD_SAMPLE,
    q.myId AS quartier_myId
    FROM immobilier_ad_blank AS t2
    LEFT JOIN Adresse AS a ON t2.adresse_id=a.id
    LEFT JOIN Quartier AS q ON a.quartier_id=q.id
    GROUP BY quartier_myId
) AS A1 ON q.myId = A1.quartier_myId
CROSS JOIN (
    SELECT AVG(t2.prix) AS HMEAN, 
    STD(t2.prix) AS STD_SAMPLE,
    q.myId AS quartier_myId
    FROM immobilier_ad_blank AS t3
    Other Joins
    Group By Other conditions
) AS A2 ON OtherConditions
CROSS JOIN (
    SELECT AVG(tN.prix) AS HMEAN, 
    STD(tN.prix) AS STD_SAMPLE,
    q.myId AS quartier_myId
    FROM immobilier_ad_blank AS tN
    Other Joins
    Group By Other conditions
) AS AN ON OtherConditions

这是在mysql中，按照手动cross join = inner join = join。然而，据我所知，交叉连接是表 X 表，但在我的例子中，因为它返回聚合，我想它不是 t^2 而是 t*(聚合数)对吗？

我在正确的轨道上吗？优化明智？我是否将过滤放在正确的位置？

我基本上想选择所有行，找到它们的连接值的平均值函数，计算它们的 Z 分数，并在不到 5 秒的时间内将它们加起来，数据量约为 100 万。

编辑 1:

我会简化我的问题，

SELECT 
((t1.prix-A1.HMEAN)*100/A1.STD_SAMPLE) AS Z,
((t1.prix-A2.HMEAN)*100/A2.STD_SAMPLE) AS Z2,
q.myId AS q_myId,
c.myId AS c_myId,
s.myId as size_myId
FROM immobilier_ad_blank AS t1 
LEFT JOIN Size AS s ON t1.size_id=s.id
LEFT JOIN Adresse AS a ON t1.adresse_id=a.id
LEFT JOIN City AS c ON a.city_id=c.id
LEFT JOIN Quartier AS q ON a.quartier_id=q.id
CROSS JOIN (
    SELECT AVG(t2.prix) AS HMEAN, 
    STD(t2.prix) AS STD_SAMPLE,
    q.myId AS quartier_myId
    s.myId as size_myId
    FROM immobilier_ad_blank AS t2
    LEFT JOIN Size AS s ON t2.size_id=s.id
    LEFT JOIN Adresse AS a ON t2.adresse_id=a.id
    LEFT JOIN Quartier AS q ON a.quartier_id=q.id
    GROUP BY quartier_myId, size_myId
) AS A1 ON q.myId = A1.quartier_myId
AS A2 on s.myId=A2.size_myId #<--------- Is this line possible ?

编辑 3:

我最终使用了临时表并复制了它们，因为在我的案例中， View 比临时表慢得多，即使底层数据已正确索引。

CREATE TEMPORARY TABLE IF NOT EXISTS A1 AS (
SELECT AVG(t2.prix) AS HMEAN, 
    STD(t2.prix) AS STD_SAMPLE,
    s.myId AS size_myId,
    q.myId AS quartier_myId
    FROM immobilier_ad_blank AS t2
    LEFT JOIN Size AS s ON t2.size_id=s.id
    LEFT JOIN Adresse AS a ON t2.adresse_id=a.id
    LEFT JOIN Quartier AS q ON a.quartier_id=q.id
    GROUP BY quartier_myId,size_myId);

CREATE TEMPORARY TABLE A2 LIKE A1; 
INSERT A2 SELECT * FROM A1;

SELECT 
((c.prix-A1.HMEAN)*100/A1.STD_SAMPLE) AS Z1,
((c.prix-A2.HMEAN)*100/A2.STD_SAMPLE) AS Z2,
q.myId AS quartier_myId,
s.myId AS size_myId
FROM immobilier_ad_blank AS c
LEFT JOIN Size AS s ON c.size_id=s.id
LEFT JOIN Adresse AS ad ON c.adresse_id=ad.id
LEFT JOIN Quartier AS q ON ad.quartier_id=q.id
JOIN A1 on A1.quartier_myId = q.myId
JOIN A2 AS A2 on A2.size_myId = s.myId

基本上，加入同一个虚拟表(子查询)=== Views ||临时表老实说，我最终可能会创建一个永久表并每天更新一次......

Answer 1

CROSS JOIN ... ON condition 使交叉连接成为普通的 INNER JOIN 或者，为简洁起见，只是 JOIN。所以不用担心组合爆炸。正确编写的 ON 条件会阻止它。简而言之，忘掉 CROSS JOIN。只需编写 JOIN。

你可以像这样写你的JOIN

 FROM table_a AS a
 JOIN table_b AS b ON a.id = b.id

或者，您可以将 table_a 或 table_b 或两者表示为子查询。例如，您可以编写此查询。

 FROM table_A AS a
 JOIN (
         SELECT MAX(id) id, district
           FROM table_b
          GROUP BY district
      ) AS b on a.id = b.id

换句话说，您可以交替使用物理表 (table_b) 或虚拟表(子查询)。

在您的例子中，您的子查询是一个包含四列的表:(HMEAN、STD_SAMPLE、quartier_myId 和 size_myId)。

您将如何将该表连接到查询中的其余表？

你有密码

  JOIN (subquery) AS A1 ON q.myId = A1.quartier_myId 

但是，要使用子查询的第四列 (size_myId)，您还需要将它用于 JOIN。为此，请将 AND 放在 ON 子句中。做这样的事情:

  JOIN (subquery) AS A1   ON q.myId = A1.quartier_myId 
                         AND s.myId = A.size_myId

如果 s.myId 的含义是正确的，那应该会给你一个有用的结果。

复合 ON 子句非常有用。你可以这样做:

  LEFT JOIN (subquery) AS A1   ON q.myId = A1.quartier_myId 
                              AND s.myId = A.size_myId
                              AND A.HMEAN > 7.5

过滤你的结果。