My data looks like this: http://sqlfiddle.com/#!2/1266b2/1
我的数据如下所示:http://sqlfiddle.com/#!2/1266b2/1
But I want it to look like:
但我希望它看起来像:
+------------+------+---+
| 2015-01-01 | walk | 1 |
| 2015-01-01 | run | 0 |
| 2015-01-01 | bike | 0 |
| 2015-01-02 | walk | 0 |
| 2015-01-02 | run | 0 |
| 2015-01-02 | bike | 0 |
| 2015-01-03 | walk | 0 |
| 2015-01-03 | run | 1 |
| 2015-01-03 | bike | 0 |
| 2015-01-04 | walk | 0 |
| 2015-01-04 | run | 0 |
| 2015-01-04 | bike | 0 |
| 2015-01-05 | walk | 0 |
| 2015-01-05 | run | 1 |
| 2015-01-05 | bike | 0 |
+------------+------+---+
Each day should have a collection of how many occurrences of each event response happened.
每天应该收集每个事件响应发生的次数。
- The dates are collected from a calendar table and are thus purely static.
- The event names are numerous and likely to change.
- Event responses join events for name and other context rules.
日期从日历表中收集,因此纯粹是静态的。
事件名称很多,可能会发生变化。
事件响应为名称和其他上下文规则加入事件。
Would be a big help to figure this out. At least offer tips for better title (search terms) so I can figure out how to resolve this issue.
想出这个将是一个很大的帮助。至少提供更好的标题(搜索条件)的提示,以便我可以弄清楚如何解决这个问题。
This is for processing graphs at activezoo.com. Any advice for other approaches or methods for analyzing data is very welcome.
这是为了处理activezoo.com上的图表。对于分析数据的其他方法或方法的任何建议都是非常受欢迎的。
1 个解决方案
#1
5
It sounds like you want every date/event combination and you want to count the # of event responses. If so, use a cross join between the calendar and the event table to get every combination and then left join to the event_responses table and count a column from that table so you only count matches.
听起来您想要每个日期/事件组合,并且您想要计算事件响应数。如果是这样,请使用日历和事件表之间的交叉连接来获取每个组合,然后将其连接到event_responses表并计算该表中的列,以便您只计算匹配项。
SELECT calendar.date AS date, events.name AS event, COUNT(event_responses.date) AS count
FROM events
CROSS JOIN calendar
LEFT JOIN event_responses ON event_responses.event_id = events.id
AND event_responses.date = calendar.date
GROUP BY calendar.date, event
ORDER BY calendar.date, event
#1
5
It sounds like you want every date/event combination and you want to count the # of event responses. If so, use a cross join between the calendar and the event table to get every combination and then left join to the event_responses table and count a column from that table so you only count matches.
听起来您想要每个日期/事件组合,并且您想要计算事件响应数。如果是这样,请使用日历和事件表之间的交叉连接来获取每个组合,然后将其连接到event_responses表并计算该表中的列,以便您只计算匹配项。
SELECT calendar.date AS date, events.name AS event, COUNT(event_responses.date) AS count
FROM events
CROSS JOIN calendar
LEFT JOIN event_responses ON event_responses.event_id = events.id
AND event_responses.date = calendar.date
GROUP BY calendar.date, event
ORDER BY calendar.date, event