如何通过sql consult修复此顺序？如何更改饼图元数据库中的维度标签？

Question

我一直在尝试在元数据库问题中构建一个饼图，放到我的仪表板中。我有player_id和updated_at列，并且我执行了一次咨询来返回每天的updated_at数量。我这样做有一个问题，我不能使用order by，我不知道为什么，但这不起作用。所以，我想知道我的错误在哪里。

SQL：

SELECT * FROM (
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-01 00:00:00' and '2020-02-01 23:59:59' union 
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-02 00:00:00' and '2020-02-02 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-03 00:00:00' and '2020-02-03 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-04 00:00:00' and '2020-02-04 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-05 00:00:00' and '2020-02-05 23:59:59' 
) AS x
ORDER BY d asc

列updated_at类型为timestamp。一个值例是: 2020-02-01 00:47:32，另一个问题是为什么我不能在元数据库中设置Dimession和Meassure，我想将Dimession label设置为各自的date interval，并在Meassure标签中设置count number。有可能吗？

​

谢谢，祝你有愉快的一天。

Answer 1

为什么不使用group by

SELECT date(updated_at, '%Y-%m-%d') AS d, 
       count(DISTINCT player_id) AS c
FROM matchmaking_stats_summary
WHERE updated_at >= '2020-02-01 00:00:00' and 
      updated_at < '2020-02-06 00:00:00' 
GROUP BY date(updated_at, '%Y-%m-%d') ;

Answer 2

我一直在修改我的sql代码，我终于找到了一种方法来处理日期间隔和玩家数量，如饼图、元数据库维度和度量。

select date(updated_at), count(DISTINCT updated_at) as jogadores
    from matchmaking_stats_summary
        group by date(updated_at)
        order by date(updated_at) asc;

非常感谢。