Book pick up last time, recently left home is really boring, in addition to look at the book of the examination, playing dnf handball, can only write code, the results last night hanging in a platform of a technology for sale someone order, probably the business is the need to help tutor some of the interviews need to be used in the SQL.
Looking back, I've also received more than $3w in SQL on that platform, and examined those categories, I'm going to start a new topic to make some examples and summarize the topics I've encountered, and I welcome any corrections.
Today's Topic: Querying Continuous Data
take
Scenario 1: need to query the maximum continuous check-in length, generally used in the toc scenario
Scenario 2: Querying the dates when the continuous flow of people exceeds 2w, the business management system of an amusement park
reasoning
The idea I generally use for this kind of topic is to move to static and continuous to unchanged.
For example, Scenario 2, you need to find consecutive dates, then you need to find another consecutive variable, so that the two variables can be subtracted or other operations to get the unchanged data, and then you can query by grouping or partitioning.
example
Stadium Traffic Flow Chart
| listings | typology |
|---|---|
| id | int |
| visit_date | date |
| people | int |
visit_date is a column with a unique value in the table.
Daily foot traffic information is recorded in these three columns: serial number (id), date (visit_date), and people (people).
There is only one row of records per day, the id is incremented with the date, and the date is not necessarily incremented consecutively.
Write SQL to find three or more rows that have a daily headcount greater than or equal to 100 and consecutive dates. Returns a table of results sorted in ascending order by visit_date.
Scheme:
Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int)
Truncate table Stadium
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', 10)
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', 109)
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', 150)
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', 99)
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', 145)
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', 1455)
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', 199)
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', 188)
example explanation
A table of foot traffic in a gymnasium requires that data be found for a minimum of three consecutive days where the foot traffic exceeds 100, and according to the
The visit_date is sorted in ascending order.
real combat
Review a function in SqlServer before doing the problem
DATEADD(DAY,2,'2020-03-27');
The function can be added or subtracted from a date and can perform the date portion of the addition or subtraction, with DAY indicating an increase in the number of days.
Along the lines of thought, the date is continuous, so you can generate a column of row numbers, use the date to make a difference with the row number, and if the results are equal then it means it is continuous.
Solving Problems Generating Line Numbers
You need to create your own dynamic columns, e.g. add your own row number columns
As for why you don't use IDs, ids are also incremental, we'll talk more about that when we're done.
SELECT ,t1.visit_date, FROM (
SELECT t.*, COUNT(1) OVER(partition by DATEADD(DAY, * -1,t.visit_date)) AS daysCount FROM
(SELECT *,ROW_NUMBER() Over (ORDER BY id) as rowId FROM Stadium where people >= 100) t
) t1 WHERE > =3
move
1. Filtering flow of people greater than or equal to 100 data, and through the partition function to increase the number of lines
2. Subtract the line number with the date to get a date, equal to the date is continuous, again through the partition function based on the date to get the number of partitions
3. Screening partition number greater than or equal to 3 is three consecutive days or more than three days of foot traffic is greater than or equal to 100 of the data
4. Process as a subquery result to get the result.
in the end
Why don't you use ids or need to create your own row numbers. Because after filtering out the data with insufficient foot traffic, the result of the equivocation between id and time will still not change, and you will get the wrong data, and the data that does not satisfy itself will still be queried.