我需要计算满足其他条件的行定义的间隔中包含的满足特定条件的行数。示例：“reference”之间的行数n（值为1和4，满足条件“other\u condition”=b）为n=1，“reference”之间的行数n（值为2和5，满足条件“other\u condition”=b）为n=2，以此类推。

Date          Reference    Other_condition
20171111            1              a
20171112            2              a   
20171113            3              b
20171114            4              b  
20171115            5              b

我通过hive/impalasql查询访问数据库，不幸的是，我不知道从哪里开始实现这样的窗口函数。我想要的半伪代码版本是这样的：

SELECT COUNT (DISTINCT database.Date) AS counter, Other_condition, reference
FROM database
WHERE database.Other_condition = a AND database.Reference BETWEEN 
(window function condition 1: database.Reference = 2) AND 
(window function condition 2: database.Reference = 5)
GROUP BY counter