(
Id NUMBER (10),
The Name varchar (5),
Rq DATE
)
The data shown in the following
Id Name Rq
1 A 2020-03-01
2 A 2020-03-31
3 A 2020-04-01
4 A 2020-04-05
5 B 2020-03-21
6 B 2020-03-26
7 C 2020-03-28
8 C 2020-03-30
9 C 2020-04-02
10 C 2020-04-03
11 D 2020-03-29
12 D 2020-03-31
13 D 2020-04-07
I need is to enter the commencement date, such as 6, 20200228202040 take out 10 days appear more than three times the data, expect the data displayed below
2 A 2020-03-31
3 A 2020-04-01
4 A 2020-04-05
7 C 2020-03-28
8 C 2020-03-30
9 C 2020-04-02
10 C 2020-04-03
Give everyone a great god to SQL, they checked the along while, also not a clue
CodePudding user response:
The count over, pay attention to the range between the window parametersCodePudding user response:
SELECT
*
The FROM
(
SELECT
A. ID,
A. the NAME,
A. rq,
CASE
S the biggest ishu & gt;=3 THEN
'Y'The ELSE
'N'
END AS flag
The FROM
A
LEFT the JOIN (
SELECT
ID,
COUNT (NAME) AS cishu
The FROM
A. AS a1
INNER JOIN A AS a2 ON a1. NAME=a2. The NAME
AND a1. Rq BETWEEN a1. Rq - 10
AND a1. Rq + 10
GROUP BY
ID
ID=B) ON a. b. ID
)
WHERE
Flag='Y'
CodePudding user response:
The above write wrongSELECT
*
The FROM
(
SELECT
A. ID,
A. the NAME,
A. rq,
CASE
S the biggest ishu & gt;=3 THEN
'Y'The ELSE
'N'
END AS flag
The FROM
A
LEFT the JOIN (
SELECT
A1. ID,
COUNT (A2) NAME) AS cishu
The FROM
A. AS a1
INNER JOIN A AS a2 ON a1. NAME=a2. The NAME
AND a1. Rq BETWEEN a1. Rq - 10
AND a1. Rq + 10
WHERE
A1. Rq BETWEEN '20200228'
AND '20200406'
GROUP BY
ID
ID=B) ON a. b. ID
)
WHERE
Flag='Y'
CodePudding user response:
Above the or write wrong, will be subject to this, the idea should be made clear, even if you take the wrong, change yourself,,SELECT
*
The FROM
(
SELECT
A. ID,
A. the NAME,
A. rq,
CASE
S the biggest ishu & gt;=3 THEN
'Y'The ELSE
'N'
END AS flag
The FROM
A
LEFT the JOIN (
SELECT
A1. ID,
COUNT (A2) NAME) AS cishu
The FROM
A. AS A1
INNER JOIN A AS A2 ON A1. NAME=A2. The NAME
AND A2. Rq BETWEEN A1. Rq - 10
AND A1. Rq + 10
WHERE
A1. Rq BETWEEN '20200228'
AND '20200406'
GROUP BY
A1. ID
ID=B) ON a. b. ID
)
WHERE
Flag='Y'
CodePudding user response:
with tab1 as (
Select id 1, 'a' nam, the date '2020-04-01' rq from dual union all
Select 2, 'a' nam, the date '2020-04-02' rq from dual union all
Select 3, 'a' nam, the date '2020-04-03' rq from dual union all
Select 4, 'a' nam, the date '2020-04-15' rq from dual union all
Select 5, 'a' nam, the date '2020-04-15' rq from dual union all
Select 6, 'a' nam, the date '2020-04-27' rq from dual union all
Select 7, 'a' nam, the date '2020-04-28' rq from dual union all
Select 8, 'a' nam, the date '2020-04-28' rq from dual union all
Select 9, 'a' nam, the date '2020-05-01' rq from dual
)
Tab2 as (
Select a t1. *,
Row_number () over (partition by t1. Nam order by t1. Rq, t1, id) rn,
Count (1) over (partition by t1. Nam order by t1. Rq range between the current row and 10 following) reaching
The from tab1 t1
The order by t1. Rq
)
, tab3 as (
Select a t1. *,
Lag (t1) reaching) over (partition by t1. Nam order by t1. Rn) lag
The from tab2 t1
)
, tab4, as (
Select a t1. *
The from tab3 t1
Where a t1. Reaching & gt; NVL (t1) lag, 1)
And t1. Reaching & gt;=3
)
The select t1. Id, t1. Nam, t1, rq from tab3 t1, tab4, t2
Where a t1. Nam=t2. Nam
And t1. An rn between t2. Rn and t2. Rn + t2. Reaching - 1
The order by t1. Id
CodePudding user response:
One more violent searchwith tab1 as (
Select id 1, 'a' nam, the date '2020-04-01' rq from dual union all
Select 2, 'a' nam, the date '2020-04-02' rq from dual union all
Select 3, 'a' nam, the date '2020-04-03' rq from dual union all
Select 4, 'a' nam, the date '2020-04-15' rq from dual union all
Select 5, 'a' nam, the date '2020-04-15' rq from dual union all
Select 6, 'a' nam, the date '2020-04-27' rq from dual union all
Select 7, 'a' nam, the date '2020-04-28' rq from dual union all
Select 8, 'a' nam, the date '2020-04-28' rq from dual union all
Select 9, 'a' nam, the date '2020-05-01' rq from dual
)
Tab2 as (
The select t2. *,
Count (1) over (partition by t1. Nam, t1, id) cot
The from tab1 t1, tab1 t2
Where a t1. Nam=t2. Nam
And t1. Rq + 10 & gt;=t2. Rq
And t1. Rq & lt;=t2. Rq
The order by t1. Id, t2. Id
)
Select distinct t1. Id, t1. Nam, t1. Rq
The from tab2 t1 where t1. Cot & gt;=3
;