PLSQL-群函数的参数化定义

发布于 2024-10-31 22:26:41 字数 727 浏览 0 评论 0原文

我有一个表，我想在其中应用一些聚合函数，以特定列的特定元素的分组为标准。假设下面的例子：

TIMESTAMP   SITE    VALUE

10:00            A  100
10:00            B  50
10:00            C  25
10:00            D  25
10:05            A  25
10:05            B  15
10:05            C  5
10:05            D  10

我想找到按 SITE、TIMESTAMP 分组的平均值，但站点 A、B 我希望将其估计为 1，这意味着我想要用 A 替换 B 然后求平均值：

TIMESTAMP   SITE    VALUE
10:00            A  75
10:00            A  75
10:00            C  25
10:00            D  25
10:05            A  20
10:05            A  20
10:05            C  5
10:05            D  10

一种方法是更新表设置 B=A，但如果我想要的话，这不是一个复杂的解决方案未来可对更多SITES进行分组

原文

I have a table where i want to apply some aggregation functions having as criteria the grouping of spesific element of specific column. Assume the example below:

TIMESTAMP   SITE    VALUE

10:00            A  100
10:00            B  50
10:00            C  25
10:00            D  25
10:05            A  25
10:05            B  15
10:05            C  5
10:05            D  10

i want to find the average value grouping by SITE, TIMESTAMP but the site A,B i want it to be estimated as one, meaning that i wanted to REPLACE B WITH A have AND THEN find the average:

TIMESTAMP   SITE    VALUE
10:00            A  75
10:00            A  75
10:00            C  25
10:00            D  25
10:05            A  20
10:05            A  20
10:05            C  5
10:05            D  10

One way is to update the table setting B=A, but this is not such a sophisticated solution if i want in future to group more SITES

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冷夜 2024-11-07 22:26:41

您不需要更新表。

SELECT CASE site WHEN 'B' THEN 'A' ELSE site END as site
      ,timestamp
      ,AVG(value)
FROM sites
GROUP BY CASE site WHEN 'B' THEN 'A' ELSE site END
        ,timestamp
;

You don't need to update the table.

SELECT CASE site WHEN 'B' THEN 'A' ELSE site END as site
      ,timestamp
      ,AVG(value)
FROM sites
GROUP BY CASE site WHEN 'B' THEN 'A' ELSE site END
        ,timestamp
;

回复收藏 0 原文

浮云落日 2024-11-07 22:26:41

您可以分别找到未组合和组合站点的平均值，然后将它们合并在一起，就像这样 -

SELECT timestamp, site, AVG(value) FROM
(
    -- uncombined
    SELECT timestamp, site, value FROM TABLE WHERE site NOT IN ('A', 'B')

    UNION ALL

    -- combined A, B
    SELECT timestamp, 'AB' site, AVG(value) val
    FROM TABLE
    GROUP BY timestamp
    WHERE site IN ('A', 'B') 
) U
GROUP BY timestamp, site

只是一个想法，未测试 SQL

You can find the averages of uncombined and combined sites separately and then union them togeather, like this -

SELECT timestamp, site, AVG(value) FROM
(
    -- uncombined
    SELECT timestamp, site, value FROM TABLE WHERE site NOT IN ('A', 'B')

    UNION ALL

    -- combined A, B
    SELECT timestamp, 'AB' site, AVG(value) val
    FROM TABLE
    GROUP BY timestamp
    WHERE site IN ('A', 'B') 
) U
GROUP BY timestamp, site

Just an idea, not tested the sql

回复收藏 0 原文

~没有更多了~