Name performance date
A 500 2020-1-1
A 1000 2020-1-15
A 2000 2020-2-1
A 1500 2020-2-11
A 600 2020-3-11
A 1600 2020-3-15
Require statistical someone's performance standard (& gt;=1000), for two months only 1 time,
As data, A staff, in the 2020-1-15 mark, so need to mark, on February 15th and after to meter 1
According to the above data, the actual number standard for employees 2
Don't know what a great god can help you to the algorithm, using a cursor efficiency is too low,
CodePudding user response:
WITH TMP AS
(SELECT the 'A' NAME
, 500 score
, the DATE '2020-1-1' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 1000 score
, the DATE '2020-1-15' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 2000 score
, the DATE '2020-2-1' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 1500 score
, the DATE '2020-2-11' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 600 score
, the DATE '2020-3-1' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 1600 score
, the DATE '2020-3-11' da
The FROM dual
UNION ALL
SELECT the 'A' NAME
, 2000 score
, the DATE '2020-3-15' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 1500 score
, the DATE '2020-1-1' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 1000 score
, the DATE '2020-1-15' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 2000 score
, the DATE '2020-2-1' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 1500 score
, the DATE '2020-2-11' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 600 score
, the DATE '2020-3-1' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 1600 score
, the DATE '2020-3-11' da
The FROM dual
UNION ALL
SELECT 'B' NAME
, 500 score
, the DATE '2020-4-1' da
The FROM dual)
SELECT a T1. NAME
, and COUNT (DISTINCT da1)
The FROM TMP t1
, SELECT the NAME
Da1, add_months (S, RN 1)
Add_months (S, RN) da2
The FROM (SELECT LEVEL RN FROM DUAL CONNECT BY LEVEL & lt;=12)
, SELECT the NAME
S, MIN (t.d a)
The FROM TMP t
WHERE t.s core & gt;=1000
A GROUP BY NAME))
WHERE a t1. Score & gt;=1000
AND T1. NAME=A.N AME
AND t1. Da BETWEEN da1 AND da2 0.00001
GROUP BY T1. The NAME;
CodePudding user response:
with tab1 as
(select the 'a' name
, 500 score
, the date '2020-1-1' da
The from dual
Union all
Select the 'a' name
, 1000 score
, the date '2020-1-15' da
The from dual
Union all
Select the 'a' name
, 2000 score
, the date '2020-2-1' da
The from dual
Union all
Select the 'a' name
, 1500 score
, the date '2020-2-11' da
The from dual
Union all
Select the 'a' name
, 600 score
, the date '2020-3-1' da
The from dual
Union all
Select the 'a' name
, 1600 score
, the date '2020-5-11' da
The from dual
Union all
Select the 'a' name
, 1600 score
, the date '2020-5-11' da
The from dual
Union all
Select the 'a' name
, 1600 score
, the date '2020-5-12' da
The from dual
Union all
Select the 'a' name
, 2000 score
, the date '2020-3-15' da
The from dual
Union all
Select 'b' name
, 1500 score
, the date '2020-1-1' da
The from dual
Union all
Select 'b' name
, 1000 score
, the date '2020-1-15' da
The from dual
Union all
Select 'b' name
, 2000 score
, the date '2020-2-1' da
The from dual
Union all
Select 'b' name
, 1500 score
, the date '2020-2-11' da
The from dual
Union all
Select 'b' name
, 600 score
, the date '2020-3-1' da
The from dual
Union all
Select 'b' name
, 1600 score
, the date '2020-3-11' da
The from dual
Union all
Select 'b' name
, 500 score
, the date '2020-4-1' da
The from dual)
Tab2 as (
Select t1. The name, Max (t1) score) score, t1. Da, row_number () over (partition by t1. The name order by t1. Da) rn
The from tab1 t1 group by t1. The name, t1. Da
)
, cur (name, score, da, rn) as (
The select t1. The name, t1. Score, t1. Da, t1. Rn
The from tab2 t1,
(select t2. The name, min (t2) rn) rn from tab2 t2 where t2. The score & gt;=1000 group by t2. Name) t2
Where a t1. Name=t2. The name
And t1. Rn=t2. Rn
Union all
Select a t1. The name,
First_value (t1) score) over (partition by t1. The name order by t1. Rn),
First_value (t1. Da) over (partition by t1. The name order by t1. Rn),
First_value (t1. Rn) over (partition by t1. The name order by t1. Rn)
The from tab2 t1, cur t2
Where a t1. Score & gt;=1000
And t1. Da & gt; Add_months (t2. Da, 1)
And t1. Name=t2. The name
And t1. An rn & gt; T2. Rn
)
Select distinct t1. * from cur t1 order by t1. The name, t1. Da
CodePudding user response: