postgreGroupby 使用 Grafana 的 (TimescaleDB) SQL 插件进行多重连接的问题

Question

我正在使用Grafana的SQL插件来查询TimescaleDB数据库。

DB将天气信息存储为

| timestamp | location_id | data_type_id | value |

其中location_id和data_type_id是描述位置的表locations和定义测量类型(温度、相对湿度…)的weather_data_types的外键

我想查询按位置和类型分组的时间范围的数据。

我设法按其中一个分组，但不能同时按两个分组。

这项工作按位置分组：

SELECT
$__timeGroupAlias("timestamp", $__interval),
avg(value),
locations.name
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
GROUP BY 1, locations.name
ORDER BY 1

这是按类型工作和分组的：

SELECT
$__timeGroupAlias("timestamp", $__interval),
avg(value),
weather_data_types.name
FROM weather_data
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, weather_data_types.name
ORDER BY 1

这不起作用：

SELECT
$__timeGroupAlias("timestamp", $__interval),
avg(value),
locations.name,
weather_data_types.name
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.name
ORDER BY 1

更具体地说，我得到以下错误

Value column must have numeric datatype, column: name type: string value: relative_humidity

似乎第三次groupby(无声地)没有发生，并且返回了weather_data_types.name，Grafana抱怨这是因为它无法绘制字符串。

更改此项以返回(整数)id，而不是删除错误消息

SELECT
$__timeGroupAlias("timestamp", $__interval),
avg(value),
locations.name,
weather_data_types.id
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.id
ORDER BY 1

但绘制了两个系列：avg和id，这表明未应用按类型分组。

我的查询有什么问题吗？这是Grafana插件的问题吗？

---

我认为这并不重要，但这是用SQLAlchemy定义的模型，希望是不言自明的。

class Location(Base):
__tablename__ = "locations"
id = sqla.Column(sqla.Integer, primary_key=True)
name = sqla.Column(sqla.String(80), unique=True, nullable=False)
country = sqla.Column(sqla.String(80), nullable=False)
latitude = sqla.Column(sqla.Float(), nullable=False)
longitude = sqla.Column(sqla.Float(), nullable=False)

class WeatherDataTypes(Base):
__tablename__ = "weather_data_types"
id = sqla.Column(sqla.Integer, primary_key=True)
name = sqla.Column(sqla.String(80), unique=True, nullable=False)
description = sqla.Column(sqla.String(500), nullable=False)
unit = sqla.Column(sqla.String(20), nullable=False)
min_value = sqla.Column(sqla.Float)
max_value = sqla.Column(sqla.Float)

class WeatherData(Base):
__tablename__ = "weather_data"
timestamp = sqla.Column(sqla.DateTime(timezone=True), primary_key=True)
location_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('locations.id'),
nullable=False,
primary_key=True
)
location = sqla.orm.relationship('Location')
type_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('weather_data_types.id'),
nullable=False,
primary_key=True
)
type = sqla.orm.relationship('WeatherDataTypes')
value = sqla.Column(sqla.Float)

Answer 1

直接向postgresql发送请求帮助我了解发生了什么。

显然，当查询返回一列值和一列字符串时，Grafana插件假设要绘制值，字符串列将用作绘制的标签。

我认为插件使用groupby来提取列，使其成为标签信息，但这种魔力对两个字符串列不起作用，因为插件本身不会连接值。因此，插件抱怨第二个字符串列不是数字，这有点误导，因为它不会抱怨第一个字符串列。

我可以通过将我用于groupby的值连接到一列中来实现它：

SELECT
time_bucket('21600s',"timestamp") AS "time",
avg(value),
CONCAT(locations.name, ' ', weather_data_types.name) AS "name"
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.name
ORDER BY 1

这将返回

time          |        avg         |           name            
------------------------+--------------------+---------------------------

插件可以正确地解释它。