能否通过视图实现按时间范围统计NLP搜索表关键词词频

完全可以通过视图实现该统计需求，根据是否需要动态调整统计时间范围，对应有两种实现方式：

前置说明 #

现有表结构如下：

create table nlp.search(response string, words string,inquiry_time timestamp);

需求核心是将每行的words字段按空格拆分出独立搜索词，按response分组统计每个搜索词的出现次数，最终拼接为单词(次数)的格式输出，同时支持按inquiry_time过滤统计范围。

方案1：普通视图（适配固定统计时间范围/查询时自行加过滤条件） #

如果不需要动态传入时间参数，或者可以接受查询视图时额外加时间过滤，直接用普通视图即可：

视图定义SQL（以Spark SQL/Hive语法为例） #

create view nlp.response_word_stats as
-- 内层先拆分单词、按响应+单词+时间分组统计次数
with word_split as (
    select 
        response,
        word,
        inquiry_time,
        count(*) as appear_cnt
    from nlp.search
    -- 按空格拆分words字段，一行转多行
    lateral view explode(split(words, ' ')) temp as word
    group by response, word, inquiry_time
)
-- 外层按响应分组，拼接为指定格式的统计结果
select 
    response,
    inquiry_time,
    concat_ws(' ', collect_list(concat(word, '(', appear_cnt, ')'))) as search_word_stats
from word_split
group by response, inquiry_time;

查询用法 #

如果要限定时间范围，查询时直接加过滤条件即可：

select response, search_word_stats from nlp.response_word_stats 
where inquiry_time >= TIMESTAMP("2021-09-19 00:00:00+00") 
and inquiry_time < TIMESTAMP("2021-09-21 00:00:00+00");

查询结果和需求示例完全一致，对应how to reset password的统计结果为reset(2) word(1) password(1) passphrase(1)。

方案2：参数化视图（适配动态时间范围筛选） #

如果需要直接传入时间参数查询，绝大多数主流SQL引擎（Spark SQL、BigQuery、PostgreSQL等）都支持参数化视图（也叫表值函数），可以直接把时间范围作为入参：

参数化视图定义SQL #

create view nlp.response_word_stats_by_time(start_time timestamp, end_time timestamp) as
with word_split as (
    select 
        response,
        word,
        count(*) as appear_cnt
    from nlp.search
    lateral view explode(split(words, ' ')) temp as word
    -- 直接在视图逻辑里加时间过滤
    where inquiry_time >= start_time and inquiry_time < end_time
    group by response, word
)
select 
    response,
    concat_ws(' ', collect_list(concat(word, '(', appear_cnt, ')'))) as search_word_stats
from word_split
group by response;

查询用法 #

调用时直接传入时间范围即可，不用额外加过滤条件：

select * from nlp.response_word_stats_by_time(
    TIMESTAMP("2021-09-19 00:00:00+00"),
    TIMESTAMP("2021-09-21 00:00:00+00")
);

内容的提问来源于stack exchange，提问作者Lakshmi