#sql #ssms
#sql #ssms
Вопрос:
Я не могу использовать реальный пример, но вот пример некоторых данных, которые у меня могут быть:
ПРИМЕЧАНИЕ: Это уже упорядочено по метке времени, с самой старой строкой в первой (верхней) строке и самой новой строкой в последней (нижней) строке.
------------ --------------------- ---------------- -------------
| Product ID | Status | Days in Status | Team |
------------ --------------------- ---------------- -------------
| 1a | designing | 4 | Engineering |
| 1a | gathering materials | 6 | Engineering |
| 1a | creating | 20 | Engineering |
| 1a | testing function | 5 | QA |
| 1a | testing usability | 10 | QA |
| 1a | designing | 4 | Engineering |
| 1a | recreating | 3 | Engineering |
| 1a | testing usability | 3 | QA |
| 1a | testing function | 1 | QA |
| 1a | testing | 4 | QA |
------------ --------------------- ---------------- -------------
В этом примере я хотел бы найти количество дней, в течение которых продукт 1a был с QA в последнее время (не общее количество дней, в течение которых он был с QA). Это было бы 3 1 4 . Я также хочу найти количество дней, в течение которых это было с разработкой в последнее время. Это было бы 4 3 = 7.
У кого-нибудь есть идея, как это сделать?
———— редактировать вот лучший пример:
------------ -------------- ----------------------- ---------------- -------------
| Product id | date | status | days in status | team |
------------ -------------- ----------------------- ---------------- -------------
| 1a | Jan 1 2020 | designing | 4 | engineering |
| 1a | Jan 5 2020 | gathering materials | 4 | engineering |
| 1a | Jan 9 2020 | creating | 2 | engineering |
| 1a | Jan 11 2020 | testing functionality | 3 | QA |
| 1a | Jan 14 2020 | testing usability | 5 | QA |
| 1a | Jan 19 2020 | gathering materials | 1 | engineering |
| 1a | Jan 20 2020 | creating | 3 | engineering |
| 1a | Jan 23 2020 | testing functionality | 1 | QA |
| 1a | Jan 24 2020 | testing usability | 1 | QA |
------------ -------------- ----------------------- ---------------- -------------
Я хочу получить количество дней, в течение которых 1a был последним с QA (1 1), и количество дней, в течение которых QA ожидал разработки непосредственно перед этим (1 3)
Комментарии:
1. Пожалуйста, определите «совсем недавно». В ваших данных нет столбца, который определяет порядок.
2. о, извините за это. самая старая находится в первой строке, а самая новая — в последней строке
3. . . Нет такой вещи. Таблицы SQL неупорядочены.
4. Нет ни «первой строки», ни «окончательного raw». Таблицы базы данных не отсортированы по своей природе.
5. в моем фактическом наборе данных у меня есть куча временных меток, и я уже упорядочил их по временной метке. Я добавлю пример, который ясно показывает это.
Ответ №1:
Если вам конкретно нужна последняя группа, вы можете использовать not exists :
select sum(days_in_status)
from t
where not exists (select 1
from t
where t2.status <> t.status and t2.timestamp > t.timestamp
);
Если вам нужна последняя группа контроля качества, я бы предложил:
select t.*
from (select t.*,
dense_rank() over (partition by status order by seqnum - seqnum_2) as ranking
from (select t.*,
row_number() over (order by timestamp desc) as seqnum,
row_number() over (partition by status order by timestamp desc) as seqnum_2
from t
) t
) t
where status = 'QA' and ranking = 1;
Комментарии:
1. Спасибо за вашу помощь!
Ответ №2:
Теперь я знаю, как проводить сеансы в базе данных, поддерживающей функции OLAP, давайте попробуем это здесь снова.
Нам нужен новый идентификатор сеанса, больший, чем предыдущий, каждый раз, когда команда меняется с течением времени, а затем суммировать дни для наибольшего идентификатора сеанса, который мы получаем.
WITH
-- your input, enriched by a timestamp ...
input(Product_ID,Status,Days_in_Status,Team,ts) AS (
SELECT '1a','designing' , 4,'Engineering',TIMESTAMP '2020-07-05 12:21:01'
UNION ALL SELECT '1a','gathering materials', 6,'Engineering',TIMESTAMP '2020-07-11 12:22:02'
UNION ALL SELECT '1a','creating' ,20,'Engineering',TIMESTAMP '2020-07-31 12:23:03'
UNION ALL SELECT '1a','testing function' , 5,'QA' ,TIMESTAMP '2020-08-05 12:24:04'
UNION ALL SELECT '1a','testing usability' ,10,'QA' ,TIMESTAMP '2020-08-15 12:02:05'
UNION ALL SELECT '1a','designing' , 4,'Engineering',TIMESTAMP '2020-08-19 12:03:06'
UNION ALL SELECT '1a','recreating' , 3,'Engineering',TIMESTAMP '2020-08-22 12:04:07'
UNION ALL SELECT '1a','testing usability' , 3,'QA' ,TIMESTAMP '2020-08-25 12:05:08'
UNION ALL SELECT '1a','testing function' , 1,'QA' ,TIMESTAMP '2020-08-26 12:06:09'
UNION ALL SELECT '1a','testing' , 4,'QA' ,TIMESTAMP '2020-08-30 12:07:10'
)
-- end of your input. In the real query, replace the following comma with 'WITH'
,
-- A counter that is at 1 every time the team changes, else at 0
with_counter AS (
SELECT
*
, CASE WHEN LAG(team) OVER(ORDER BY ts) <> team
THEN 1
ELSE 0
END AS counter
FROM input
)
,
-- a running sum of the counter of the query just above, as session id ...
with_session AS (
SELECT
*
, SUM(counter) OVER(ORDER BY ts) AS sessionid
FROM with_counter
)
-- finally, group by the session id, and grab the last (biggest) session id.
-- LIMIT 1 in certain databases,
-- in SQL server, you go 'SELECT TOP 1' instead of 'SELECT'
-- and do without the final LIMIT 1 clause at the end.
SELECT
sessionid
, SUM(days_in_status)
FROM with_session
WHERE team='QA' -- don't know if this filter is needed ...
GROUP BY
sessionid
ORDER BY 1 DESC LIMIT 1;
-- out sessionid | sum
-- out ----------- -----
-- out 3 | 8