SQL:将时间戳与仅限时间的参数相匹配，以对多天内的唯一时间进行分组和计数

Question

使用SQL或Pyspark，我想在2个月的时间框架内计算时间戳中的唯一次数。我想看看将行记录到表中的频率分布。这是因为我知道00:00:00的时间戳占很大比例，但我想知道与其他时间相比有多大以及比例。

此查询对最常见的日期时间进行分组和计数，但我需要排除日期，并且只有时间。显然，这样做并不常见

select timestamp,
count(*) as count
from table_name
where timestamp between '2021-01-01' and '2021-02-28'
group by 1
order by 2 desc

SQL/Pyspark在齐柏林飞船笔记本中的Spark DB上运行。

时间戳如下：2021-01-01 02:07:55

Answer 1

也许是这样的？

select 
date_format(timestamp, "H m s") as dataTime,
count(*) as count
from table_name
where timestamp between '2021-01-01' and '2021-02-28'
group by date_format(timestamp, "H m s") 
order by 2 desc

带保留字的名称字段(timestamp(不是一个好主意。

来自火花文档。

Answer 2

根据timestamp列的类型，如果它是TimestampType(用lpad加前导零(，则可以提取小时、分钟、秒，如果是StringType，则可以使用regexp_extract

from pyspark.sql import functions as F
# if your ts column has TimestampType
(df
.withColumn('ts', F.col('ts').cast('timestamp')) # my assumption ts is timestamp
.withColumn('time_only', F.concat(
F.lpad(F.hour('ts'), 2, '0'),
F.lit(':'),
F.lpad(F.minute('ts'), 2, '0'),
F.lit(':'),
F.lpad(F.second('ts'), 2, '0')
))
.show()
)
# if your ts column is StringType
(df
.withColumn('ts', F.col('ts').cast('string')) # my assumption ts is string
.withColumn('time_only', F.regexp_extract('ts', 'd{2}:d{2}:d{2}', 0))
.show()
)
# +-------------------+---------+
# |                 ts|time_only|
# +-------------------+---------+
# |2019-01-15 03:00:00| 03:00:00|
# |2019-01-15 20:00:00| 20:00:00|
# |2019-01-15 19:00:00| 19:00:00|
# |2019-01-15 11:00:00| 11:00:00|
# +-------------------+---------+