hive - 在 Hive 中查找一年的最大出现次数
问题描述
这是一些信用卡记录,我需要找到卡到期日期最多的年份。由于日期不是YYYY/MM/DD
格式,所以在模式中我将日期定义为“字符串”类型
Card Type Full Name,Card Holder's Name,Issue Date,Expiry Date,Card PIN
Discover,Brenda D Peterson,01/2017,01/2022,1998
Diners Club International,Dawn U Reese,12/2015,12/2013,3915
Diners Club International,Helen P Perry,02/2007,02/2020,2319
American Express,Christine E Kim,08/2011,08/2013,9017
解决方案
此查询将为您提供记录数最多的年份。
select
substr(expiry_date, instr(expiry_date, '/')+1) as expiry_year,
count(*) as cnt
from customers
group by substr(expiry_date, instr(expiry_date, '/')+1)
order by cnt desc
limit 1;
在这种情况下,您可以使用以下查询,有可能超过 1 年有相同数量的卡在那一年到期
select
substr(expiry_date, instr(expiry_date, '/')+1) as expiry_year
from customers
having count(*) = (
select max(cnt) from (
select
substr(expiry_date, instr(expiry_date, '/')+1) as expiry_year,
count(*) as cnt
from customers
group by substr(expiry_date, instr(expiry_date, '/')+1)
)t
);
推荐阅读
- ruby-on-rails - Ruby - 是否可以将方法别名为安全导航运算符
- django - Line "host all all 0.0.0.0/0 md5" deleted from the pg_hba.conf file at the same time every day
- safari - Safari service worker work incorrect with custom offline page
- r - 按值选择列和以下 3 列
- c# - WPF C# - adding controls dynamically to XAML from custom class
- scala - How can I convert a single Spark Row to a Scala object?
- ios - iOS: Posting rotated image
- oracle - Specify a relative path as infile for a .ctl
- ruby-on-rails - 如何从数据库中提取评论并在网格视图中显示
- php - 从 WooCommerce 订单中获取项目元数据以显示它