我正在尝试选择一些流量数据并为不同的流量方向创建总和,在 60 秒存储桶中存储。 简化表如下(时间戳为 unix 纪元):
Timestamp Source Destination Count
1 inside outside 5
2 inside outside 6
3 outside inside 7
65 inside inside 4
66 inside outside 6
72 inside outside 7
当前查询(执行分桶,但与方向无关)
SELECT sum(count) AS total FROM table GROUP BY round(timestamp/60)
这给了我每个 60 秒"桶"的总数,例如
Count
18
10
7
现在,我迷路了(我可以在客户端代码中执行此操作,但如果可能的话,我有点想用 SQL 来做到这一点)。我想为每一行都有一个称为入站的总和,其中内部作为源,dest 作为外部,对于源外部和内部为dest的每一行,一个称为出站的总和,以及一行称为内部,其中源和dest都在里面。 仍按 60 秒存储桶分组。 即我想要回来的是:
Inbound Outbound Internal
7 11 0
0 6 4
0 7 0
我的 SQL foo 很弱,我真的不知道从哪里开始(我怀疑我需要一个子选择,但我不确定如何构建它)。
SELECT
round(timestamp/60) AS bucket,
SUM(
CASE WHEN source = 'inside' AND destination = 'outside'
THEN count ELSE 0
END
) AS outbound,
SUM(
CASE WHEN source = 'outside' AND destination = 'inside'
THEN count ELSE 0
END
) AS inbound,
SUM(
CASE WHEN source = 'inside' AND destination = 'inside'
THEN count ELSE 0
END
) AS internal
FROM
yourTable
GROUP BY
round(timestamp/60)
你实际上应该能够做一个透视:
SELECT SUM(count) AS total
, SUM(CASE
WHEN Source = 'outside'
AND Destination = 'inside'
THEN count -- Use 1 if you only want to count each row
ELSE 0 END) AS [Inbound]
, SUM(CASE
WHEN Source = 'inside'
AND Destination = 'outside'
THEN count
ELSE 0 END) AS [Outbound]
, SUM(CASE
WHEN Source = 'inside'
AND Destination = 'inside'
THEN count
ELSE 0 END) AS [Internal]
FROM table
GROUP BY round(timestamp/60)