MySQL ONLY_FULL_GROUP_BY 启用了 CONCAT

Question

我在数据库 mysql (5.7.21) 中有一个表，如下所示:

+----------+--------------+-----------+-----------+| id_price | id_reference | price_usd | unix_time |+----------+--------------+-----------+-----------+

And I need to extract the average price (price_usd) grouped by week of year, or month (unix_time).

I prepare this query:

SELECT CONCAT(WEEKOFYEAR(FROM_UNIXTIME(unix_time)),
  '-',
  YEAR(FROM_UNIXTIME(unix_time))) as date,
  AVG(price_usd) AS "model"
FROM price_avg
INNER JOIN reference ON reference.id_reference=price_avg.id_reference
WHERE price_avg.id_reference=1
GROUP BY WEEKOFYEAR(FROM_UNIXTIME(unix_time)), 
  YEAR(FROM_UNIXTIME(unix_time)),
  price_avg.id_reference
ORDER BY unix_time ASC 

内部联接对于获取具有 ID 的产品名称很有用。

我收到这个错误:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'name_of_db.price_avg.unix_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我无法更改 MySQL 的设置(我无法禁用 ONLY_FULL_GROUP_BY 模式或其他任何模式)。

我必须如何更改查询才能在 MySQL 5.7.21 中提取数据？

提前致谢。

Answer 1

您可以使用子查询，这样它将成为一个完整的分组依据。

Select `date`, 
        AVG(price_usd) AS "model"
From (
   SELECT CONCAT(WEEKOFYEAR(FROM_UNIXTIME(unix_time)),
      '-',
    YEAR(FROM_UNIXTIME(unix_time))) as `date`,
    price_usd
FROM price_avg
INNER JOIN reference ON reference.id_reference=price_avg.id_reference
WHERE price_avg.id_reference=1
) t
GROUP BY `date`
ORDER BY substring(`date`, -4), substring(`date`, 1, 2) ASC;

Result:
date    model
48-1998 11.99
36-2001 19.99