HiveSQL映射到每一周第一天、每个月第一天，每一年第一天

映射到每一周第一天：date为例如20210101字符串

SELECT
	DATE_SUB (
		to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ),
		PMOD ( DATEDIFF ( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), '2021-11-29' ), 7 ) 
	) AS dates,
	sum( NVL( traffic, 0 ) ) AS traffic,
	round( sum( NVL( load, 0 ) ) / 1000, 2 ) AS load,
	round( sum( NVL( transactionAmount, 0 ) ) / 10000, 2 ) AS charge,
	cityid,
	cityname AS cityName 
FROM
	basisdb.doorframedata A,
	middledb.historicalGantryData B 
WHERE
	( pyear = 2021 AND ( ( pmonth = 01 AND pday >= 01 AND pday <= 05 ) ) ) 
	AND A.gantryId = B.DOORFRAMECODING 
	AND cityId IN ( 130100, 130200, 130300, 130400, 130500 ) 
GROUP BY
	DATE_SUB (
		to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ),
		PMOD ( DATEDIFF ( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), '2021-11-29' ), 7 ) 
	),
	cityid,
	cityname 
ORDER BY
	cityid,
	dates

映射到每一月第一天：date为例如20210101字符串

SELECT
	trunc( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), 'MM' ) AS dates,
	sum( NVL( traffic, 0 ) ) AS traffic,
	cityid,
	cityname AS cityName 
FROM
	basisdb.doorframedata A,
	middledb.historicalGantryData B 
WHERE
	(
		pyear = 2021 
		AND (
			( pmonth = 01 AND pday >= 01 ) 
			OR ( pmonth > 01 AND pmonth < 02 ) 
			OR ( pmonth = 02 AND pday <= 01 ) 
		) 
	) 
	AND A.gantryId = B.DOORFRAMECODING 
	AND cityId IN ( 130100, 130200, 130300, 130400, 130500 ) 
GROUP BY
	trunc( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), 'MM' ),
	cityid,
	cityname 
ORDER BY
	cityid,
	dates

映射到每一年第一天：date为例如20210101字符串

SELECT
	trunc( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), 'YYYY' ) AS time,
	sum( NVL( traffic, 0 ) ) AS traffic,
	ROUND( sum( traffic ) / count( dates ), 2 ) AS averageTraffic 
FROM
	(
	SELECT
		dates,
		sum( NVL( traffic, 0 ) ) AS traffic 
	FROM
		historicalTollStationData 
	WHERE
		(
			pyear = 2021 
			AND (
				( pmonth = 01 AND pday >= 01 ) 
				OR ( pmonth > 01 AND pmonth < 02 ) 
				OR ( pmonth = 02 AND pday <= 01 ) 
			) 
		) 
		AND tollStationCoding = 'G009513005080620880' 
	GROUP BY
		dates 
	) A 
GROUP BY
	trunc( to_date( from_unixtime ( UNIX_TIMESTAMP ( dates, 'yyyyMMdd' ) ) ), 'YYYY' )