时间戳

首页 > 解决方案 > 我正在尝试在 sql 中运行查询，在其中将字段更改为小写以使其一致并对不同字段求和

mysql - 我正在尝试在 sql 中运行查询，在其中将字段更改为小写以使其一致并对不同字段求和

问题描述

我有具有站点搜索的字段 evar4，文本在不同的情况下导致搜索相同单词的两个不同行，例如

evar4  searches
games   3236
Games    564

我想将所有 evar4 转换为小写字段并为所有相同的术语显示一个值，即，

e.g.
evar4  searches
games   3800

我已将字段转换为小写，但我不确定如何在 sql 查询中对这些字段进行求和，并且只显示同一单词的一行。

Select lcase(evar4), count (evar4) as searches from adobe_hits
where (date(date_time) between '2019-01-01' and '2019-01-31')
and post_prop46 like ('www:')
and exclude_hit like '0'
and hit_source not in (5,7,8,9)
group by evar4
order by searches desc;

sample result: 

Original Result:
evar4                 searches
games                   3236
recently played games   1326
mahjongg solitaire  633
Games                   564

Result after lcase: 
lcase(evar4)            searches
games                   3236
recently played games   1326
mahjongg solitaire  633
games                   564

desired result: 
lcase(evar4)            searches
games                   3800
recently played games   1326
mahjongg solitaire  633

标签： mysqlsqldatabricks

解决方案

您需要按字段的小写版本进行分组。

Select lcase(evar4), count (evar4) as searches from adobe_hits
where (date(date_time) between '2019-01-01' and '2019-01-31')
and post_prop46 like ('www:')
and exclude_hit like '0'
and hit_source not in (5,7,8,9)
group by lcase(evar4)
order by searches desc;

如果为表达式分配别名，则可以按该别名进行分组。

Select lcase(evar4) as l_evar4, count (evar4) as searches from adobe_hits
where (date(date_time) between '2019-01-01' and '2019-01-31')
and post_prop46 like ('www:')
and exclude_hit like '0'
and hit_source not in (5,7,8,9)
group by l_evar4
order by searches desc;

另一个避免首先这样做的解决方案是声明该列使用不区分大小写的排序规则。然后所有比较，包括分组，都将忽略大小写。

推荐阅读