sql - SQlite 填充空单元格

Question

我正在写这个查询来计算。

SELECT
Date,
SUM(CASE WHEN (Terms="Sandwich") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Sandwich") THEN 1 ELSE 0 END) AS Sandwich,
SUM(CASE WHEN (Terms="Chicken") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Chicken") THEN 1 ELSE 0 END) AS Chicken,
SUM(CASE WHEN (Terms="Meat") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Meat") THEN 1 ELSE 0 END) AS Meat,
SUM(CASE WHEN (Terms="Others") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Others") THEN 1 ELSE 0 END) AS Others
FROM __table__
GROUP BY Date

原始数据是这样的。

这是查询的结果。

/ 用于 int 除法，所以为什么结果是浮点数。第二个问题是有时项的计数和总和为 0，因此 0/0 未定义。我想用 0 填充这些单元格。请更新查询。

标签： sqlsqlite

Answer 1

也许你可以在AVG这里使用：

SELECT
    Date,
    AVG(CASE WHEN Terms = 'Sandwich' THEN Stars END) AS Sandwich,
    AVG(CASE WHEN Terms = 'Chicken'  THEN Stars END) AS Chicken,
    AVG(CASE WHEN Terms = 'Meat'     THEN Stars END) AS Meat,
    AVG(CASE WHEN Terms = 'Others'   THEN Stars END) AS Others
FROM __table__
GROUP BY
    Date;