mysql - 如何使用 View 执行复杂的 sql 查询，而不是依赖中间(具体)表

Question

SQL 爱好者:

我正在尝试通过玩以下用例来挖掘我一些生疏的 sql 技能:

假设我们有一家有线电视公司，并且有跟踪的数据库表:

• 电视节目，
• 观看我们节目的客户，以及
• 观看事件(特定客户观看特定节目的日期)。

假设我们想要一份关于每个节目收到的观看次数以及平均年龄的报告观众。我在这里意识到的关键是，如果同一个人在不同日期两次观看节目 X我们不能让这个人的年龄在“X 节目观众的平均年龄”计算中占两倍。

首先我定义我的表并在其中添加一些数据(这是 mysql 语法，b.t.w):

    drop table if exists shows ;

    create table shows (
        showid int   not null,
        showname varchar(256) not null,
        primary key (showid)
    );


    drop table if exists cust ;

    create table cust (
        custid int   not null,
        custname varchar(256) not null,
        age int   not null,
        primary key (custid)
    );

    drop table if exists watched ;

    create table watched (
        date   int   not null,
        showid int   not null,
        custid int   not null,
        primary key (custid, showid, date)
    );


    insert into shows values (1, 'bingo');
    insert into shows values (2, 'animals');


    insert into cust values (1, 'joe', 20);
    insert into cust values (2, 'bob', 30);
    insert into cust values (3, 'mary', 40);


    insert into cust values (4, 'lou', 20);

                              # date  / show /     cust 

    insert into watched values (1,       1,         1);  
    insert into watched values (1,       1,         2);
    insert into watched values (1,       1,         3);
    insert into watched values (2,       2,         2);

    insert into watched values (2,       1,         1);
    insert into watched values (3,       1,         1);
    insert into watched values (4,       1,         1);
    insert into watched values (1,       1,         4);

现在我创建一个连接表的查询，并为我提供每个客户的节目名称和年龄谁看过节目。

     select date, shows.showid, cust.custid, showname, age  from 
        ->                       watched 
        ->                           inner join
        ->                       cust
        ->                           on cust.custid = watched.custid
        ->                               inner join 
        ->                                   shows
        ->                               on shows.showid = watched.showid    ;
    +------+--------+--------+----------+-----+
    | date | showid | custid | showname | age |
    +------+--------+--------+----------+-----+
    |    1 |      1 |      1 | bingo    |  20 |
    |    2 |      1 |      1 | bingo    |  20 |
    |    3 |      1 |      1 | bingo    |  20 |
    |    4 |      1 |      1 | bingo    |  20 |
    |    1 |      1 |      2 | bingo    |  30 |
    |    1 |      1 |      3 | bingo    |  40 |
    |    1 |      1 |      4 | bingo    |  20 |
    |    2 |      2 |      2 | animals  |  30 |
    +------+--------+--------+----------+-----+
    8 rows in set (0.00 sec)

但注意customer id 1作为watcher出现了多次“宾果游戏”节目的成员，我希望他只被数一次。

因此，我创建了一个查询来列出节目和观看过节目的客户，但只对每个客户计数一次。

    mysql>  select age, showname, showid, custid  from 
        ->              ( select date, shows.showid, cust.custid, showname, age  from 
        ->                  watched 
        ->                      inner join
        ->                  cust
        ->                      on cust.custid = watched.custid
        ->                          inner join 
        ->                              shows
        ->                          on shows.showid = watched.showid    
        ->              ) as VIEWS
        ->          group by custid, showname;
    +-----+----------+--------+--------+
    | age | showname | showid | custid |
    +-----+----------+--------+--------+
    |  20 | bingo    |      1 |      1 |
    |  30 | animals  |      2 |      2 |
    |  30 | bingo    |      1 |      2 |
    |  40 | bingo    |      1 |      3 |
    |  20 | bingo    |      1 |      4 |
    +-----+----------+--------+--------+
    5 rows in set (0.00 sec)

下一步——以及(这是我希望您能就此向我提出建议的事情)...我试图创建一个 View ，为我提供所观看的每个节目的名称、观看节目的人的平均年龄以及节目 ID。我计划将其与一个查询结合起来，该查询为我提供了每个节目的观看次数。但是 View 创建失败，如图所示:

    mysql> create view viewages as 
        -> select showname, avg(age), showid
        ->       from 
        ->          (select age, showname, showid, custid  from 
        ->              ( select date, shows.showid, cust.custid, showname, age  from 
        ->                  watched 
        ->                      inner join
        ->                  cust
        ->                      on cust.custid = watched.custid
        ->                          inner join 
        ->                              shows
        ->                          on shows.showid = watched.showid    
        ->              ) as VIEWS
        ->          group by custid, showname)
        ->              as DISTINCT_CUST_VIEWS
        ->          group by showname;
    ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

好吧..所以那没用。我让它工作了，但我做的方式似乎很俗气。我使用中间表。

有没有 sql rock star 可以告诉我一个更好的方法来做到这一点，没有表..也许有一个观点，就像我试过的那样创造，或者更好的东西。？

这是我蹩脚的解决方案:

drop table if exists viewage ;

create table viewage (
    showname varchar(256) not null,
    avg_age   float  not null,
    showid    int    not null
);

insert into viewage 
select showname, avg(age), showid
      from 
         (select age, showname, showid, custid  from 
             ( select date, shows.showid, cust.custid, showname, age  from 
                 watched 
                     inner join
                 cust
                     on cust.custid = watched.custid
                         inner join 
                             shows
                         on shows.showid = watched.showid    
             ) as VIEWS
         group by custid, showname)
             as DISTINCT_CUST_VIEWS
         group by showname;


##  Finally join the table with average age for each show with a query that does the count of views for each show:



drop table if exists viewage ;

create table viewage (
    showname varchar(256) not null,
    avg_age   float  not null,
    showid    int    not null
);

insert into viewage 
select showname, avg(age), showid
      from 
         (select age, showname, showid, custid  from 
             ( select date, shows.showid, cust.custid, showname, age  from 
                 watched 
                     inner join
                 cust
                     on cust.custid = watched.custid
                         inner join 
                             shows
                         on shows.showid = watched.showid    
             ) as VIEWS
         group by custid, showname)
             as DISTINCT_CUST_VIEWS
         group by showname;



select count(*), showname, avg_age from  
  watched 
    inner join 
  viewage
    on viewage.showid = watched.showid 
    group by showname;




+----------+----------+---------+
| count(*) | showname | avg_age |
+----------+----------+---------+
|        1 | animals  |      30 |
|        7 | bingo    |    27.5 |
+----------+----------+---------+
2 rows in set (0.00 sec)

在此先感谢您的帮助!

-克里斯

Answer 1

I tried to create a view that gives me the name of each show viewed, the average age of those who watched the show, and the show id.

错误信息很清楚-- Subqueries cannot be used in the FROM clause of a view.

这是解决这个特定问题的一种方法。

-- Age of each show's customers.
create or replace view show_cust_ages as 
select distinct watched.showid, cust.custid, cust.age
from watched 
inner join cust  on cust.custid = watched.custid;

-- Average age of show's customers. This queries the previous view.
create or replace view show_avg_ages as
select showid, avg(age) avg_age
from show_cust_ages
group by showid;

-- Your goal.
create or replace view show_name_avg_ages as
select t1.showid, t2.showname, t1.avg_age
from show_avg_ages t1
inner join shows t2 on t2.showid = t1.showid;

在生产中，我会花更多的时间思考事物的名称，而不是在这里。

您应该知道，在 MySQL 中，基于 View 的 View 在大表上可能表现不佳。