启动学习SQL，并需要知道它如何执行%

Question

我做错了什么？它能实现吗？我需要显示“所有收入的汇总”、“所有收入的计数”、“平均收入”和“按id计算的平均收入”。

SELECT `sum_revenue`, `allcnt_revenue`, `avg_revenue`, count(`cnt_revenue`) 
    FROM (SELECT sum(`revenue`) as sum_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION
    FROM (SELECT count(`revenue`) as allcnt_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION
    FROM (SELECT avg(`revenue`) as avg_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION   
    FROM (SELECT AVG(`revenue`) as cnt_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                GROUP BY `uid`) AS `Purchases`;

错误：

您的SQL语法出现了错误；请检查与MySQL服务器版本相对应的手册，以获得正确的语法，以便使用“FROM”(选择count(revenue)作为allcnt_revenue FROM Purchases，在第8行“`re”)

在完美的世界里，我想看到：

SELECT sum(`revenue`), 
count(`revenue`), 
avg(`revenue`), 
avg(count(`revenue`) by group `uid`) 
FROM `Purchases`
WHERE `revenue` BETWEEN 0 AND 10

Answer 1

我相信count(cnt_revenue)不会工作，因为您缺少按其他字段分组，然后得到计数：

SELECT `sum_revenue`, `allcnt_revenue`, `avg_revenue`, count(`cnt_revenue`) 
    FROM (SELECT sum(`revenue`) as sum_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION
    FROM (SELECT count(`revenue`) as allcnt_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION
    FROM (SELECT avg(`revenue`) as avg_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                ) AS `Purchases`
    UNION   
    FROM (SELECT AVG(`revenue`) as cnt_revenue
                FROM `Purchases`
                WHERE `revenue` BETWEEN 0 AND 10
                GROUP BY `uid`) AS `Purchases`
GROUP BY  `sum_revenue`, `allcnt_revenue`, `avg_revenue`;

Answer 2

如果我正确地理解了您的问题，那么您要做的就是一次以两种不同粒度级别获取数据。这不是SQL数据库设计的对象(与可以返回嵌套结构的文档或图形数据库不同)。

您可以将语句拆分为两个--一个用于总计，一个用于每个uid计数--或者将汇总复制到结果的每一行，这可能处理小数据集，但一旦有多个条目，就不必要地填充内存。

SELECT
 sum(`revenue`) as 'sum_revenue',
 count(`revenue`) as 'allcnt_revenue',
 avg(`revenue`) as 'avg_revenue'
FROM `Purchases`
;

SELECT uid, count(*) as 'purchases_per_uid'
FROM `Purchases`
GROUP BY `uid`
;

之后，在应用程序代码中处理结果。

Answer 3

嗯嗯。。。我想你可以用算术做你想做的事。如果您希望每个uid的平均记录数

SELECT sum(revenue), count(revenue), avg(revenue),
       count(*) / count(distinct uid) 
FROM Purchases
WHERE revenue BETWEEN 0 AND 10;

如果你想要每个uid的平均收入

SELECT sum(revenue), count(revenue), avg(revenue),
       sum(revenue) / count(distinct uid) 
FROM Purchases
WHERE revenue BETWEEN 0 AND 10;