python - 范围内每个数字的 SQL 计数总计(编辑 : via a UDF)

Question

我在 Amazon (AWS) RedShift 中有类似以下的数据，它代表了不同人的阅读日志，每一行都是在特定日期阅读的章节跨度的记录:

| person | date   | book     | chapter_start | chapter_end |
|--------|--------|----------|---------------|-------------|
| Alice  | 7/1/15 | Big Red  | 4             | 7           |
| Bob    | 7/1/15 | Big Red  | 1             | 5           |
| James  | 7/1/15 | Big Red  | 2             | 9           |
| Tim    | 7/1/15 | Big Red  | 10            | 12          |
| Alice  | 7/2/15 | Big Red  | 8             | 10          |
| Bob    | 7/2/15 | Big Red  | 6             | 8           |
| James  | 7/2/15 | Big Red  | 10            | 11          |
| Tim    | 7/1/15 | Blue Sky | 1             | 3           |
| Alice  | 7/1/15 | Blue Sky | 3             | 4           | 

我想知道是否有一个 SQL 查询(记住它需要与 RedShift 兼容的 postgresql)可以为跨度中的每一章生成一个计数，以便结果如下所示:

| book     | chapter | count |
|----------|---------|-------|
| Big Red  | 1       | 1     |
| Big Red  | 2       | 2     |
| Big Red  | 3       | 2     |
| Big Red  | 4       | 3     |
| Big Red  | 5       | 3     |
| Big Red  | 6       | 3     |
| Big Red  | 7       | 3     |
| Big Red  | 8       | 3     |
| Big Red  | 9       | 2     |
| Big Red  | 10      | 3     |
| Big Red  | 11      | 2     |
| Big Red  | 12      | 1     |
| Blue Sky | 1       | 1     |
| Blue Sky | 2       | 1     |
| Blue Sky | 3       | 2     |
| Blue Sky | 4       | 2     |

请注意，在上面的结果中，计数不仅仅考虑了 chapter_start 和 chapter_end。例如，如果我们处理 Tim 从第 1 章到第 3 章阅读 Blue Sky 的条目，那么 Blue Sky 的第 1、2 和第 3 章应该增加各自的计数器。

编辑(2015 年 7 月 28 日):经过进一步研究，虽然 Mureinik 的以下建议似乎适用于完全兼容 postgresql 的系统，但 RedShift 仅支持一小部分 postgresql 函数(请参阅: Unsupported PostgeSQL Functions in RedShift ) 因此他的回答不适用于 RedShift。目前，这似乎无法通过用户定义的函数 (UDF) 实现，因为它们也不受支持。值得一提的是，RedShift 产品经理确认他们计划在 2015 年 9 月之前支持与 postgresql 兼容的 UDF。<< 祈祷吧 >>

综上所述，是否有人愿意通过 UDF 承担 GENERATE_SERIES() 的等价物？

编辑(2015 年 9 月 11 日):UDF 终于在 RedShift 中可用。查看最新发布的AWS RedShift UDF documentation .澄清需求——所需要的是 python 中的一组返回函数，它复制了 Postgres GENERATE_SERIES() function 的功能。 .有人愿意接受挑战吗？

编辑(2016 年 1 月 8 日): 在与 AWS 来回交流并澄清其文档后，RedShift 中支持的 UDF 似乎只能返回单个值，而不是集合.因此，目前似乎无法创建模仿此功能的 UDF。

Answer 1

你可以使用 generate_series创建 chapter_start 和 chapter_end 之间所有章节的列表，然后按它们分组并计数:

SELECT   book, chapter, COUNT(*)
FROM     (SELECT book, GENERATE_SERIES(chapter_start, chapter_end) AS chapter
          FROM   mytable) t
GROUP BY book, chapter