mysql - 如何选择具有多个连接子句的平均值？

Question

我想从我已经放在一起的 SQL 表中获取平均数(参加者 NPS)。

我将初始表封装在一个新的选择语句中，这样我就可以取不同值的平均值。我的 Join 子句中有什么东西阻止它工作吗？

我收到以下错误:

ERROR: missing FROM-clause entry for table "gizmo" Position: 12

SELECT
    avg(bigtable.gizmo.attendee_nps)
FROM
    (
        SELECT DISTINCT 
            attendee_survey_results.swoop_event_id AS "Swoop ID",
            attendee_survey_results.startup_weekend_city AS "SW City",
            swooptable.start_date AS "Date",
            gizmo.attendee_nps AS "Attendee NPS"
        FROM attendee_survey_results
        JOIN 
            ( 
                SELECT
                    swoop_event_id,
                    (
                        100 * count(CASE WHEN attendee_nps >= 9 THEN 1 END)
                         / count(attendee_nps)
                         - 100 * count(CASE WHEN attendee_nps <= 6 THEN 1 END)
                         / count(attendee_nps)
                    ) AS "attendee_nps"
                FROM attendee_survey_results
                GROUP BY swoop_event_id
            ) AS "gizmo"
            ON gizmo.swoop_event_id = attendee_survey_results.swoop_event_id

        JOIN 
            (
                SELECT eid,start_date,manager_email
                FROM events
            ) AS "swooptable"
            ON gizmo.swoop_event_id = swooptable.eid
    ) AS bigtable

Answer 1

[编辑，好的，你没有任何问题，但底部的请求应该有效]

3 部分符号 bigtable.gizmo.attendee_nps

你不能使用这个bigtable。gizmo。attendee_nps，这是“with DB”特定语法:db_name.tbl_name.col_name。

您应该使用table_or_alias.col_name_or_alias 表示法

在子查询中，您丢失了每个 deep-1 的深层表名称:

    -- with the deep explicite
SELECT `d0`.`new_field`
FROM (
        -- implicite `d1` table 
    SELECT `new_field`
    FROM (
            -- with the deep `d2` explicite and alias of field
        SELECT `d2`.`field` AS `new_field`
        FROM (
                -- without the explicite `d3` table and `field` field
            SELECT *
            FROM (
                    -- output a `field` => 12
                SELECT 12 as `field`
            ) AS `d3`
        ) AS `d2`
    ) AS `d1`
) AS `d0`

    -- print `new_field` => 12

访问 deep-1 别名字段

SELECT `attendee_nps`
FROM
    (
        SELECT `attendee_nps` AS `new_alias_field`
        FROM attendee_survey_results
    ) AS bigtable

Unknown column 'attendee_nps' in 'field list'

当你在deep-1查询中为字段取别名时，deep-0只能访问别名new_alias_field，原来的字段已经不存在了。

双引号 " 表别名

FROM (
        -- ...
    ) AS "bigtable"

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"bigtable"' at line N

MySql 不允许使用 " 来创建表别名(字段别名技术上没问题)。

您应该使用 mysql 反引号来转义表别名，例如 AS `My Table Alias`

---

正确查询:

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE events
    (`eid` int, `start_date` varchar(10), `manager_email` varchar(15))
;

INSERT INTO events
    (`eid`, `start_date`, `manager_email`)
VALUES
    (1, '2016-11-11', 'mail_1@mail.com'),
    (2, '2016-11-12', 'mail_2@mail.com'),
    (3, '2016-11-13', 'mail_3@mail.com'),
    (4, '2016-11-14', 'mail_4@mail.com'),
    (5, '2016-11-15', 'mail_5@mail.com'),
    (6, '2016-11-16', 'mail_6@mail.com'),
    (7, '2016-11-17', 'mail_7@mail.com')
;


CREATE TABLE attendee_survey_results
    (`id` int, `swoop_event_id` int, `startup_weekend_city` varchar(6), `attendee_nps` int)
;

INSERT INTO attendee_survey_results
    (`id`, `swoop_event_id`, `startup_weekend_city`, `attendee_nps`)
VALUES
    (1, 1, 'city_1', 1),
    (2, 2, 'city_2', 22),
    (3, 3, 'city_3', 3),
    (4, 1, 'city_4', 4),
    (5, 2, 'city_5', 5),
    (6, 3, 'city_6', 9),
    (7, 7, 'city_7', 17)
;

查询 1:

SELECT
    AVG(`bigtable`.`attendee_nps`)
FROM
    (
        SELECT DISTINCT 
            `asr`.`swoop_event_id` AS `Swoop ID`,
            `asr`.`startup_weekend_city` AS `SW City`,
            `swooptable`.`start_date` AS `date`,
            `gizmo`.`attendee_nps` AS `attendee_nps`
        FROM `attendee_survey_results` AS `asr`
        JOIN 
            ( 
                SELECT
                    `swoop_event_id`,
                    (
                        100 * count(CASE WHEN `attendee_nps` >= 9 THEN 1 END)
                         / count(`attendee_nps`)
                         - 100 * count(CASE WHEN `attendee_nps` <= 6 THEN 1 END)
                         / count(`attendee_nps`)
                    ) AS `attendee_nps`
                FROM `attendee_survey_results`
                GROUP BY `swoop_event_id`
            ) AS `gizmo`
            ON `gizmo`.`swoop_event_id` = `asr`.`swoop_event_id`

        JOIN 
            (
                SELECT `eid`, `start_date`, `manager_email`
                FROM `events`
            ) AS `swooptable`
            ON `gizmo`.`swoop_event_id` = `swooptable`.`eid`
    ) AS `bigtable`

Results :

| AVG(`bigtable`.`attendee_nps`) |
|--------------------------------|
|                   -14.28571429 |