Oracle/SQL - 按日按操作对项目进行分组

发布于 2024-11-09 02:36:56 字数 1007 浏览 5 评论 0原文

大家好，我有一个“小部件”表，其中包含以下列：小部件、操作、时间戳_。我们想要做的是在特定日期之间每天提取所有被拒绝多次的小部件。所以这是一个示例表

widget      action      timestamp_
-------------------------------------------
type1       reject      2011-05-10 08:00:00
type1       reject      2011-05-10 09:00:00
type1       reject      2011-05-10 09:30:00
type2       reject      2011-05-11 09:30:00
type3       reject      2011-05-11 09:30:00
type1       reject      2011-05-11 09:30:00
type1       reject      2011-05-11 09:30:00
type2       reject      2011-05-12 10:30:00
type2       reject      2011-05-12 12:30:00
type3       reject      2011-05-12 12:30:00

所以我预计希望以这两种方式之一查看结果......

在日期 x 和 y 之间，有两个小部件在一天内被拒绝多次

这将看到 type1 在一天，与 type2 一样，因此计数为：2

或

显示每个小部件及其被拒绝多次的日期和次数。示例..

widget      date            count
---------------------------------
type1       2011-05-10      3
type1       2011-05-11      2
type2       2011-05-12      2

这可能是首选输出...但是如何呢？

提前致谢！

原文

Hi all I have a 'widget' table that has the following columns: widget, action, timestamp_. What we want to do is pull all the widgets that were rejected more than once a day between certain dates. So here's an example table

widget      action      timestamp_
-------------------------------------------
type1       reject      2011-05-10 08:00:00
type1       reject      2011-05-10 09:00:00
type1       reject      2011-05-10 09:30:00
type2       reject      2011-05-11 09:30:00
type3       reject      2011-05-11 09:30:00
type1       reject      2011-05-11 09:30:00
type1       reject      2011-05-11 09:30:00
type2       reject      2011-05-12 10:30:00
type2       reject      2011-05-12 12:30:00
type3       reject      2011-05-12 12:30:00

So I anticipate wanting to see results in one of these two manners....

Between date x and y there were two widgets that were rejected multiple times in single days

This would see that type1 was rejected more than once in a day as was type2 thus the count is: 2

Display each widget along with the date that it was rejected more than once and how many times. Example..

widget      date            count
---------------------------------
type1       2011-05-10      3
type1       2011-05-11      2
type2       2011-05-12      2

This would probably be the preferred output... but how?

Thanks in advance!

分享到QQ

分享到微博

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

发布评论

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

我不会写诗 2024-11-16 02:36:56

这将给出您的输出：

SELECT Widget, to_char(timestamp_,'YYYY-MM-DD'), Count(Widget)
FROM Widget
WHERE timestamp_ BETWEEN to_date('YYYY-MM-DD HH24:MI:SS','%date1%') AND to_date('YYYY-MM-DD HH24:MI:SS','%date2%')
AND action LIKE 'reject'
GROUP BY Widget, to_char(timestamp_,'YYYY-MM-DD')
HAVING Count(Widget) > 1;

当然，您需要替换日期变量。

This would give your output:

SELECT Widget, to_char(timestamp_,'YYYY-MM-DD'), Count(Widget)
FROM Widget
WHERE timestamp_ BETWEEN to_date('YYYY-MM-DD HH24:MI:SS','%date1%') AND to_date('YYYY-MM-DD HH24:MI:SS','%date2%')
AND action LIKE 'reject'
GROUP BY Widget, to_char(timestamp_,'YYYY-MM-DD')
HAVING Count(Widget) > 1;

Of course, you'll want to replace the date variables.

回复收藏 0 原文

泡沫很甜 2024-11-16 02:36:56

尝试以下一项，您将得到您想要的：-

  Select widget,convert(varchar,[timestp],102) As Date,COUNT(timestp) as Count
  From MTest
  group by widget,[action],convert(varchar,[timestp],102)
  having COUNT(timestp)>1

Try the below one and you will get what you want :-

  Select widget,convert(varchar,[timestp],102) As Date,COUNT(timestp) as Count
  From MTest
  group by widget,[action],convert(varchar,[timestp],102)
  having COUNT(timestp)>1

回复收藏 0 原文

~没有更多了~