MySQL 5.7 新特性 generated columns

MySQL 5.7的一个新特性，generated column
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns
即generated column的值是普通column产生值，有点像视图，但是又有别于视图，因为它可以选择是否存储generated column产生的值。

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+
col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  [[NOT] NULL] [[PRIMARY] KEY]

virtual不存储值，stored存储值（并支持索引）。
但是MySQL这个特性貌似用处并不大，例如要参与计算的行只能是当前行。
在物联网中，可能存在类似的需求，但是一般会要求参与计算的行是相邻的N行，或者有规则可寻的N行。例如按照相邻的5行计算平均值，最大值，最小值，方差。
MySQL 满足不了这样的需求。

在PostgreSQL中，这不是什么新鲜概念，而且支持得更彻底。
例子：
对应 mysql vitrual generated column

postgres=# create table test(c1 int, c2 int);
CREATE TABLE
postgres=# create view v_test as select c1,c2,sqrt(c1*c2+c1*c2) from test;
CREATE VIEW
postgres=# insert into test values (1,2),(10,20);
INSERT 0 2
postgres=# select * from v_test;
 c1 | c2 | sqrt
----+----+------
  1 |  2 |    2
 10 | 20 |   20
(2 rows)

对应 mysql stored generated column

postgres=# create materialized view v_test1 as select c1,c2,sqrt(c1*c2+c1*c2) from test;
SELECT 2
postgres=# select * from v_test1;
 c1 | c2 | sqrt
----+----+------
  1 |  2 |    2
 10 | 20 |   20
(2 rows)

还有一个更适合物联网场景的，流式处理 :

pipeline=# create stream s1(c1 int, c2 int);
CREATE STREAM
pipeline=# create continuous view test as select c1,c2,sqrt(c1*c1+c2*c2) from s1;
CREATE CONTINUOUS VIEW
pipeline=# activate;
ACTIVATE
pipeline=# insert into s1 values (1,2),(10,20);
INSERT 0 2
pipeline=# select * from test;
 c1 | c2 |       sqrt
----+----+------------------
  1 |  2 | 2.23606797749979
 10 | 20 | 22.3606797749979
(2 rows)

流式处理加窗口和实时聚合 :

pipeline=# create continuous view test1 as select c1,count(*) over(partition by c1) from s1 ;
CREATE CONTINUOUS VIEW
pipeline=# create continuous view test2 as select c2,count(*) over w from s1 window w as(partition by c2);
CREATE CONTINUOUS VIEW
pipeline=# insert into s1 values (1,2);
INSERT 0 1
pipeline=# select * from test1;
 c1 | count
----+-------
  1 |     1
(1 row)

pipeline=# select * from test2;
 c2 | count
----+-------
  2 |     1
(1 row)

实时分析每个URL的访问次数，用户数，99%用户的访问延迟低于多少。

/*
 * This function will strip away any query parameters from each url,
 * as we're not interested in them.
 */
CREATE FUNCTION url(raw text, regex text DEFAULT '\?.*', replace text DEFAULT '')
    RETURNS text
AS 'textregexreplace_noopt'    -- textregexreplace_noopt@src/backend/utils/adt/regexp.c
LANGUAGE internal;  

CREATE CONTINUOUS VIEW url_stats AS
    SELECT
        url, -- url地址
    percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99,  -- 99%的URL访问延迟小于多少
        count(DISTINCT user) AS uniques,  -- 唯一用户数
    count(*) total_visits  -- 总共访问次数
  FROM
    (SELECT
        url(payload->>'url'),  -- 地址
        payload->>'user' AS user,  -- 用户ID
        (payload->>'latency')::float * 1000 AS latency_ms,  -- 访问延迟
        arrival_timestamp
    FROM logs_stream) AS unpacked
WHERE arrival_timestamp > clock_timestamp() - interval '1 day'
 GROUP BY url;  

CREATE CONTINUOUS VIEW user_stats AS
    SELECT
        day(arrival_timestamp),
        payload->>'user' AS user,
        sum(CASE WHEN payload->>'url' LIKE '%landing_page%' THEN 1 ELSE 0 END) AS landings,
        sum(CASE WHEN payload->>'url' LIKE '%conversion%' THEN 1 ELSE 0 END) AS conversions,
        count(DISTINCT url(payload->>'url')) AS unique_urls,
        count(*) AS total_visits
    FROM logs_stream GROUP BY payload->>'user', day;  

-- What are the top-10 most visited urls?
SELECT url, total_visits FROM url_stats ORDER BY total_visits DESC limit 10;
      url      | total_visits
---------------+--------------
 /page62/path4 |        10182
 /page51/path4 |        10181
 /page24/path5 |        10180
 /page93/path3 |        10180
 /page81/path0 |        10180
 /page2/path5  |        10180
 /page75/path2 |        10179
 /page28/path3 |        10179
 /page40/path2 |        10178
 /page74/path0 |        10176
(10 rows)  

-- What is the 99th percentile latency across all urls?
SELECT combine(p99) FROM url_stats;
     combine
------------------
 6.95410494731137
(1 row)  

-- What is the average conversion rate each day for the last month?
SELECT day, avg(conversions / landings) FROM user_stats GROUP BY day;
          day           |            avg
------------------------+----------------------------
 2015-09-15 00:00:00-07 | 1.7455000000000000000000000
(1 row)  

-- How many unique urls were visited each day for the last week?
SELECT day, combine(unique_urls) FROM user_stats WHERE day > now() - interval '1 week' GROUP BY day;
          day           | combine
------------------------+---------
 2015-09-15 00:00:00-07 |  100000
(1 row)  

-- Is there a relationship between the number of unique urls visited and the highest conversion rates?
SELECT unique_urls, sum(conversions) / sum(landings) AS conversion_rate FROM user_stats
    GROUP BY unique_urls ORDER BY conversion_rate DESC LIMIT 10;
 unique_urls |  conversion_rate
-------------+-------------------
          41 |  2.67121005785842
          36 |  2.02713894173361
          34 |  2.02034637010851
          31 |  2.01958418072859
          27 |  2.00045348712296
          24 |  1.99714899522942
          19 |  1.99438839453606
          16 |  1.98083502184886
          15 |  1.87983011139079
          14 |  1.84906254929873
(1 row)