javascript - 在 CubeJS 中实现 DISTINCT ON
问题描述
我有一个像这样的 Postgres 表,其中包含设备 ID、时间戳和当时设备的状态:
dev_id | timestamp | status
----------------------------------------
1 | 2020-08-06 23:00:00 | 1
2 | 2020-08-06 23:00:00 | 0
3 | 2020-08-06 23:00:00 | 1
2 | 2020-08-06 23:05:00 | 1
3 | 2020-08-06 23:05:00 | 0
1 | 2020-08-06 23:10:00 | 0
我想在他们各自的最新时间戳中查看有多少设备正在运行,有多少没有运行。在 Postgres 中,我可以DISTINCT ON
像这样使用和编写查询:
SELECT status, COUNT(status)
FROM
(
SELECT DISTINCT ON (dev_id) dev_id,
timestamp,
status
FROM
sample_metrics_data
ORDER BY
dev_id,
timestamp DESC
) sub
GROUP BY status;
这将导致:
value | count
---------------
0 | 2
1 | 1
(2 个设备,#1 和 #3,状态为 0,而 1,#2,状态为 1。)如何在 CubeJS 中创建类似的东西?支持,如果不支持,解决方法是DISTINCT ON
什么?
或者,可以使用内部联接编写查询:
SELECT status,
Count(status)
FROM sample_metrics_data
JOIN (SELECT dev_id id,
Max(timestamp) ts
FROM sample_metrics_data
GROUP BY dev_id) max_ts
ON timestamp = max_ts.ts
AND dev_id = max_ts.id
GROUP BY status;
我需要进行内部连接,但似乎只有 LEFT JOIN 可用。
解决方案
在您的情况下,如果您需要构建一个有多少设备在线的图表,那么您的问题的典型解决方案是
- 构建一个立方体,其中会有关于在线设备数量变化的数据。
- 使用rollingWindow创建度量
例如,我在你的问题中做了一个表格
并创建这个立方体
cube(`SampleMetricsData`, {
sql: "SELECT *, device_status - COALESCE(LAG(device_status) OVER (PARTITION BY id ORDER BY timemark ASC), 0) as rolling_status FROM ab_api_test.sample_metrics ORDER BY `sample_metrics`.`timemark` DESC",
measures: {
rollingStatusTotal: {
sql: `rolling_status`,
type: `sum`,
rollingWindow: {
trailing: `unbounded`,
},
},
},
dimensions: {
id: {
sql: `id`,
type: `number`,
primaryKey: true
},
timemark: {
sql: `timemark`,
type: `time`
},
}
});
在这个多维数据集上,您可以使用此查询查看在线设备图表
{"measures":["SampleMetricsData.rollingStatusTotal"],"timeDimensions":[{"dimension":"SampleMetricsData.timemark","granularity":"hour","dateRange":"This month"}],"order":{},"dimensions":[],"filters":[]}
可能您应该看到本教程,它看起来与您的任务类似。还有一个相关的问题是here
笔记
您还可以编写这样的查询来从您的数据创建一个多维数据集。但这不是最佳实践
select * from (
SELECT DISTINCT ON (dev_id) dev_id,
timestamp,
status
FROM
sample_metrics_data
ORDER BY
dev_id,
timestamp DESC
) as sample_metrics
推荐阅读
- reactjs - 无法在返回 jsx 中访问 Vue 实例
- serialization - 从 MFC 对话框保存文件
- python - Scrapy 产生深度为 2 的请求
- java - 未选中将 java.io.Serializable 强制转换为 java.util.ArrayList
- java - Tomcat 无法安装错误:Service was not (uninstalled)
- python - 从css节点scrapy中提取文本
- php - 根据前一个选择框在选择框中显示内容
- rstudio-server - 无法连接到安装在 HDInsight 群集中的 R-studio
- node.js - 在远程网站上运行 VPN 客户端
- php - Symfony 函数渲染参数动态