要求：依然使用SQL获取这4个特征：文章字数、图片数量、文章类型、发布时间距离计算时间的时间差。创建表：dwb_news.article_base_info来存储这些特征，我们选择全量的文章的数据。

原表（我的数据是在presto中进行查询的hive表，因为hive底层是mr程序，运行太慢了，还是presto查询速度快一点）

select * from ods_news.news_article limit 3;

查询文章字数

Step1：用正则表达式提取文章的中文，然后使用array_join使用，把文字连起来

select

article_id,

array_join(regexp_extract_all(content, '[u4e00-u9fa5]+'),';') as article_num from ods_news.news_article

where

logday < format_datetime(now(), 'yyyyMMdd')

and

article_id <> '' and content <> '';

Step2求字数，加一个length（）

select

article_id,

length(array_join(regexp_extract_all(content, '[u4e00-u9fa5]+'),';')) as article_num from ods_news.news_article

where

logday < format_datetime(now(), 'yyyyMMdd')

and

article_id <> '' and content <> '';

图片数量

思路是：（全文长度（就是包括标签）- 非图片标签 ）/img标签的length

Select article_id,(length(content)- length(replace(content, 'm from ods_news.news_article

where

logday < format_datetime(now(), 'yyyyMMdd')

and

article_id <> '' and content <> '';

类型名：数据表中有这个类型名，所以不用计算，直接查询

select typename from ods_news.news_article;

发布时间距离计算时间的时间差(最难)

考虑一般使用date_diff()函数

但是这个时间类型得转换一下

发布时间的处理：

（1）使用split函数要前半段。(因为presto的下标是1开始，所以就是下标1)

select

article_id, type_name, split(pub_time, 'T')[1]  from ods_news.news_article

where

logday < format_datetime(now(), 'yyyyMMdd')

and

article_id <> '' and content <> '';

（2）字符串转date类型

split(pub_time, 'T')[1]还是一个字符串 ，就需要使用case(字符串 as date)

select

article_id, type_name, cast(split(pub_time, 'T')[1] as date) from ods_news.news_article

where

logday < format_datetime(now(), 'yyyyMMdd')

and

article_id <> '' and content <> '';

（3）使用date_diff（）函数

就是发布时间和今天时间日期类型都要一致，我把发布时间转成date，今天也要转成date

查询今天时间：

Select now() as date from ods_news.news_article;

然后就使用date_diff函数把发布时间和今天时间连起来吧

Select date_diff('day', cast(split(pub_time, 'T')[1] as date), cast(now() as date)) as pub_gap from ods_news.news_article;

其实如果是昨天或者前天数据可以使用以上方法！

介于时间相差值太大，所以就换了一个方式使用logdate

(1)转了一下格式，其实logdate是我们表里面之前创建就带了

Select date_parse(logday, '%Y%m%d') from ods_news.news_article;

然后再格式化一下

Select format_datetime(date_parse(logday, '%Y%m%d'), 'yyyy-MM-dd') from ods_news.news_article;

使用date_diff函数

Select  date_diff('day', cast(format_datetime(date_parse(logday, '%Y%m%d'), 'yyyy-MM-dd') as date), cast(now() as date)) as pub_gap

from ods_news.news_article;

最后的创建表语句

create table dwb_news.article_base_info with(FORMAT='ORC')

as

with t1 as( -- 查询出所有的特征

    select

    article_id,

    length(array_join(regexp_extract_all(content, '[u4e00-u9fa5]+'),';')) as article_num,

    (length(content) - length(replace(content, '

    type_name,

    date_diff('day', cast(format_datetime(date_parse(logday, '%Y%m%d'), 'yyyy-MM-dd') as date), cast(now() as date)) as pub_gap

    from ods_news.news_article

    where

    logday < format_datetime(now(), 'yyyyMMdd')

    and

    article_id <> '' and content <> ''

)

select

article_id,

max(article_num) as article_num,

max(img_num) as img_num,

max(type_name) as type_name,

max(pub_gap) as pub_gap

from t1 group by article_id;

查询结果

看完之后，你还觉得sql简单吗？[狗头]