SQL选取某个时间段的数据及删除重复数据

1.日期时间函数格式来源SQL server
SQL选取某个时间段的数据及删除重复数据
2. Convert 函数：用于对日期变量进行格式变更。

3. 提取某个时间段的数据：
方法有很多这里只提供一种：将日期变量通过convert（）函数转化位字符变量来进行操作。

4.例子
数据源：kaggle US accident：https://www.kaggle.com/sobhanmoosavi/us-accidents
/Bulk insert 来解决海量数据导入问题/

SET language US_ENGLISH

Create table US_AC1
(ID VARCHAR(100),
“Source” VARCHAR(100),
TMC VARCHAR(100),
Severity VARCHAR(100),
Start_Time VARCHAR(100),
End_Time VARCHAR(100),
Start_Lat VARCHAR(100),
Start_Lng VARCHAR(100),
Number VARCHAR(100),
Street VARCHAR(100),
Side VARCHAR(100),
City VARCHAR(100),
County VARCHAR(100),
“State” VARCHAR(100),
Zipcode VARCHAR(100),
Country VARCHAR(100),
Timezone VARCHAR(100),
Airport_Code VARCHAR(100),
Weather_Timestamp VARCHAR(100),
“Temperature(F)” VARCHAR(100),
“Wind_Chill(F)” VARCHAR(100),
“Humidity(%)” VARCHAR(100),
“Pressure(in)” VARCHAR(100),
“Visibility(mi)” VARCHAR(100),
Wind_Direction VARCHAR(100),
“Wind_Speed(mph)” VARCHAR(100),
“Precipitation(in)” VARCHAR(100),
Weather_Condition VARCHAR(100),
Amenity VARCHAR(100),
Bump VARCHAR(100),
Crossing VARCHAR(100),
Give_Way VARCHAR(100),
Junction VARCHAR(100),
No_Exit VARCHAR(100),
Railway VARCHAR(100),
Roundabout VARCHAR(100),
Station VARCHAR(100),
“Stop” VARCHAR(100),
Traffic_Calming VARCHAR(100),
Traffic_Signal VARCHAR(100),
Turning_Loop VARCHAR(100),
Sunrise_Sunset VARCHAR(100),
Civil_Twilight VARCHAR(100),
Nautical_Twilight VARCHAR(100),
Astronomical_Twilight VARCHAR(100),
)

BULK INSERT USAAC.dbo.US_AC1
FROM ‘file location’
WITH ( FORMAT=‘CSV’);

–选取发生事故前十的日期及当日事故数目区间：2016-05-02 - 2017-05-02
–写法一–
Select top (10) CONVERT(varchar(100),Start_Time, 23) As Acc_Date
,Count(*) As Acc_Num
From US_AC
WHERE Start_Time between ‘2016-05-02’ AND ‘2017-05-02’
Group by CONVERT(varchar(100),Start_Time, 23)
Order by Acc_Num DESC

–写法二–
SELECT
Top(10) CONVERT(CHAR(11),Start_Time, 23) as ACC_Date
, Count(ID) as ACC_Q
FROM US_AC
WHERE Start_Time >= ‘2016-05-02’
AND Start_Time <‘2017-05-02’
GROUP BY CONVERT(CHAR(11),Start_Time, 23)
Order BY ACC_Q DESC

5.删除重复变量
—制造重复数据—

BULK INSERT USAAC.dbo.US_AC1
FROM ‘file location’
WITH ( FORMAT=‘CSV’,
FIRSTROW=2);

—确认重复—
Select Top(100) *, ROW_NUMBER() over( order by ID DESC) from US_AC1

–消除重复CTE方式–
With CTE AS(
Select *,ROW_NUMBER()
OVER(
PARTITION BY
ID
Order BY
ID
)row_num
From US_AC1
)
DELETE FROM CTE
WHERE row_num>1

SQL选取某个时间段的数据及删除重复数据

相关推荐