Oracle查询从子查询中获取数据

Shanky 发表于 Dev

小腿

我有以下查询来获取每人的平均计数。

SELECT
    Y.name, 
    ROUND(SUM(Y.final_count) / (31)) AVG_COUNT 
FROM
    (SELECT 
         day, name, final_count,
         NVL(last_value(decode(final_count, 0, CAST(null AS number), final_count)) ignore nulls over (order by day, final_count desc), 0) final_count
    FROM
        (WITH days (day) AS
         (
             SELECT date '2020-05-01' FROM dual
             UNION ALL
             SELECT day + 1 FROM days WHERE day < last_day(day)
         ),
         names (name) AS
         (
             SELECT DISTINCT name FROM person
         )
         SELECT
             d.day, n.name, COUNT(p.name) AS final_count
         FROM 
             days d
         CROSS JOIN
             names n
         LEFT JOIN
             person p ON p.name = n.name
                      AND p.date_created >= d.day
                      AND p.date_created < d.day + 1
         GROUP BY
             d.day, n.name
         ORDER BY
             d.day, n.name) x
     WHERE
         x.name = ( 'Person_1')) Y   
GROUP BY 
    Y.name;

在上面的查询中，我必须手动输入人员姓名，有什么办法可以从人员表中获取姓名并将上述内容作为子查询提供？

在person表中，我有不止一个人。我的最终输出应该是这样的。它适用于游标，但由于要求我不想写游标。

Person        Avg_Count
Person1       10
Person2       11
Person3       3
Person4       22

示例在这里：在此处输入链接描述

亚历克斯·普尔

只需删除该行：

where x.name= ( 'Person_1')

内部 CTE 已经获得了所有名称。

我不认为计算正在做你想要的，你让它变得相当复杂。您之前的问题似乎是 X/Y 问题...

我认为你可以这样做：

select p.name,
  round(count(*) / extract(day from last_day(date '2020-05-01'))) as avg_count
from person p
where p.date_created >= date '2020-05-01'
and p.date_created < date '2020-05-01' + interval '1' month
group by p.name
order by p.name;

这将给所有有任何行的人以及他们当月的平均值。

数据库<>小提琴

或者，如果您上一个问题的内部部分是正确的，那么只需从中获取平均值作为内联视图或其他 CTE：

with days (day) as (
  select date '2020-05-01' from dual
  union all
  select day + 1 from days where day < last_day(day)
),
names (name) as (
  select distinct name from person
  -- possibly only within the target month?
),
counts (day, name, count_person) as (
  select d.day, n.name,
    coalesce(
      last_value(nullif(count(p.name),0) ignore nulls)
        over (partition by n.name order by d.day), 0) as count_person
  from days d
  cross join names n
  left join person p on p.name = n.name
  and p.date_created >= d.day
  and p.date_created < d.day + 1
  group by d.day, n.name
)
select name, round(avg(count_person))
from counts
group by name
order by name

数据库<>小提琴

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。