51黑料不打烊

文档WorkfrontWorkfront 指南

按团队查询的活动

Last update: Tue Mar 11 2025 00:00:00 GMT+0000 (Coordinated Universal Time)
  • 主题：

创建对象：

  • 用户

您可以使用本文中的查询来创建类似于增强型分析中的数据可视化图表。

IMPORTANT
查询将产生与增强型分析中所示类似的结果，但它们可能不完全匹配。

先决条件

在开始之前，您必须

  1. 与Business Intelligence (BI)工具建立连接：

    1. 为厂苍辞飞蹿濒补办别创建搁别补诲别谤帐户或连接
    2. 建立与奥辞谤办蹿谤辞苍迟数据连接的连接

建立连接后，您可以使用本文中的查询来提取和可视化数据。

主团队用户的登录事件

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
??? uc.hometeamid,
??? TO_DATE(ul.lastlogindate),
??? count(*)
FROM users_current uc
??? INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(ul.lastlogindate)

主团队用户的登录事件：深入分析

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
??? uc.hometeamid,
??? uc.userid,
??? TO_DATE(ul.lastlogindate),
??? count(*)
FROM users_current uc
??? INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(ul.lastlogindate)

团队用户的登录事件

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
??? tmc.teamid,
??? TO_DATE(ul.lastlogindate),
??? count(*)
FROM teammembers_current tmc
??? inner join teams_current tc on tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
??? inner join userlogins ul on tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(ul.lastlogindate)

团队用户的登录事件：深入分析

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 tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate), count(*)
FROM teammembers_current tmc
??? INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
??? INNER JOIN userlogins ul ON tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate)

主团队用户的任务状态更改事件

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
??? uc.hometeamid,
??? TO_DATE(tsc.begin_effective_timestamp),
??? count(tsc.taskid) as task_status_updates
FROM users_current uc
??? INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
??? INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid,? TO_DATE(tsc.begin_effective_timestamp)

主团队用户的任务状态更改事件：深入分析

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
??? uc.hometeamid,
??? uc.userid,
??? TO_DATE(tsc.begin_effective_timestamp),
??? count(tsc.taskid) as task_status_updates
FROM users_current uc
??? INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
??? INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tsc.begin_effective_timestamp)

团队用户的任务状态更改事件

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 tmc.teamid,? TO_DATE(tsc.begin_effective_timestamp), count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
??? INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
??? INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
??? INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid,? TO_DATE(tsc.begin_effective_timestamp)

团队用户的任务状态更改事件：深入分析

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
??? tmc.teamid,
??? tmc.userid,
??? TO_DATE(tsc.begin_effective_timestamp),
??? count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
??? inner join teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
??? inner join assignments_current ac ON tmc.userid = ac.assignedtoid
??? inner join task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tsc.begin_effective_timestamp)

主团队用户的任务完成事件

WITH task_status_done 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 te
??????? WHERE status = 'CPL'
??? )
??? WHERE status != previous_status
), task_percentage_done as (
??? SELECT
??????? taskid,
??????? percentcomplete,
??????? begin_effective_timestamp
??? FROM (
??????? SELECT
??????????? taskid,
??????????? percentcomplete,
??????????? begin_effective_timestamp,
??????????? lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
??????? FROM tasks_event
??????? WHERE TO_NUMBER(percentcomplete) = 100
??? )
??? WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
??? SELECT
??????? taskid,
??????? actualcompletiondate
??? FROM tasks_event
??? WHERE actualcompletiondate is not null
)

SELECT
??? uc.hometeamid,
??? TO_DATE(tasks_done.task_completion_date),
??? count(tasks_done.taskid) as task_completed
FROM users_current uc
??? INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
??? INNER JOIN (
??????? SELECT distinct taskid, task_completion_date
??????? FROM (
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_status_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_percentage_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
??????????? FROM task_completion_date_exists
??????? )
??? ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(tasks_done.task_completion_date)

主团队用户的任务完成事件：深入分析

WITH task_status_done 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 te
??????? WHERE status = 'CPL'
??? )
??? WHERE status != previous_status
), task_percentage_done as (
??? SELECT
??????? taskid,
??????? percentcomplete,
??????? begin_effective_timestamp
??? FROM (
??????? SELECT
??????????? taskid,
??????????? percentcomplete,
??????????? begin_effective_timestamp,
??????????? lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
??????? FROM tasks_event
??????? WHERE TO_NUMBER(percentcomplete) = 100
??? )
??? WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
??? SELECT
??????? taskid,
??????? actualcompletiondate
??? FROM tasks_event
??? WHERE actualcompletiondate is not null
)

SELECT
??? uc.hometeamid,
??? uc.userid,
??? TO_DATE(tasks_done.task_completion_date),
??? count(tasks_done.taskid) as task_completed
FROM users_current uc
??? INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
??? INNER JOIN (
??????? SELECT distinct taskid, task_completion_date
??????? FROM (
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_status_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_percentage_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
??????????? FROM task_completion_date_exists
??????? )
??? ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tasks_done.task_completion_date)

团队用户的任务完成事件

WITH task_status_done 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 te
??????? WHERE status = 'CPL'
??? )
??? WHERE status != previous_status
), task_percentage_done as (
??? SELECT
??????? taskid,
??????? percentcomplete,
??????? begin_effective_timestamp
??? FROM (
??????? SELECT
??????????? taskid,
??????????? percentcomplete,
??????????? begin_effective_timestamp,
??????????? lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
??????? FROM tasks_event
??????? WHERE TO_NUMBER(percentcomplete) = 100
??? )
??? WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
??? SELECT
??????? taskid,
??????? actualcompletiondate
??? FROM tasks_event
??? WHERE actualcompletiondate is not null
)

SELECT
??? tmc.teamid,
??? TO_DATE(tasks_done.task_completion_date),
??? count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
??? INNER JOIN teams_current tc ON tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
??? INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
??? INNER JOIN (
??????? SELECT distinct taskid, task_completion_date
??????? FROM (
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_status_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_percentage_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
??????????? FROM task_completion_date_exists
??????? )
??? ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(tasks_done.task_completion_date)

团队用户的任务完成事件：深入分析

WITH task_status_done 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 te
??????? WHERE status = 'CPL'
??? )
??? WHERE status != previous_status
), task_percentage_done as (
??? SELECT
??????? taskid,
??????? percentcomplete,
??????? begin_effective_timestamp
??? FROM (
??????? SELECT
??????????? taskid,
??????????? percentcomplete,
??????????? begin_effective_timestamp,
??????????? lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
??????? FROM tasks_event
??????? WHERE TO_NUMBER(percentcomplete) = 100
??? )
??? WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
??? SELECT taskid, actualcompletiondate
??? FROM tasks_event
??? WHERE actualcompletiondate is not null
)

SELECT
??? tmc.teamid,
??? tmc.userid,
??? TO_DATE(tasks_done.task_completion_date),
??? count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
??? INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
??? INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
??? INNER JOIN (
??????? SELECT distinct taskid, task_completion_date
??????? FROM (
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_status_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
??????????? FROM task_percentage_done
??????????? UNION
??????????? SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
??????????? FROM task_completion_date_exists
??????? )
??? ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tasks_done.task_completion_date)
recommendation-more-help
5f00cc6b-2202-40d6-bcd0-3ee0c2316b43