mysql - 如何将以下 3 个 select 语句包含在单个 mySQL View 中

Question

需要创建一个执行以下 3 个 select 语句的 View

我能够在 SQL 编辑器中执行它们并获得比率。

select @lag := count(twitter_handles) from handle_impressions where today_impressions > yest_impressions;
select @decay := count(twitter_handles) from handle_impressions where today_impressions < yest_impressions;
select @ratio := @lag/@decay;

目的是让一个 View 封装 3 个 select 语句以返回滞后、衰减和比率

Answer 1

我们可以尝试使用条件聚合编写单个查询:

SELECT
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) AS lag_val,
    COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS decay_val,
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) /
        COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS ratio
FROM handle_impressions;