Query attività progetto
Puoi utilizzare le query in questo articolo per creare visualizzazioni di dati simili a quelle in Analisi avanzate.
IMPORTANT
Le query produrranno risultati simili a quelli mostrati in Analisi avanzate, ma potrebbero non corrispondere esattamente.
Prerequisiti
Prima di iniziare, è necessario
-
Stabilire una connessione con lo strumento Business Intelligence (BI):
Una volta stabilita una connessione, puoi utilizzare le query in questo articolo per estrarre e visualizzare i dati.
Eventi di accesso degli utenti Progetti
Mostra il numero di persone assegnate al progetto che hanno effettuato l’accesso in un dato giorno.
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
tds.projectid,
ads.calendardate,
count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid and TO_DATE(ul.lastlogindate) = ads.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventi di accesso utenti progetti: approfondimento
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
tds.projectid,
ul.userid,
ads.calendardate,
count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid AND TO_DATE(ul.lastlogindate) = ads.calendardate
group by tds.projectid, ul.userid, ads.calendardate
Eventi di modifica dello stato delle attività degli utenti di Progetti
Mostra il numero di persone che hanno cambiato lo stato di un'attività per il progetto in un dato giorno.
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
count(tds.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventi di modifica dello stato delle attività degli utenti di Projects: espansione
WITH task_status_changes as (
SELECT
taskid,
status,
lastupdatedbyid,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lastupdatedbyid,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
tsc.lastupdatedbyid,
count(tsc.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate
Eventi di completamento attività degli utenti di progetti
Mostra il numero di persone che hanno completato un'attività per il progetto in un dato giorno.
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status = 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
count(tds.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid and tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid and tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventi di completamento attività degli utenti di Projects: approfondimento
WITH task_status_changes as (
SELECT
taskid,
status,
lastupdatedbyid,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lastupdatedbyid,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status = 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
tsc.lastupdatedbyid,
count(tsc.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate
recommendation-more-help
5f00cc6b-2202-40d6-bcd0-3ee0c2316b43