SQL:过去 6 个月内不同月份的 4 次传输以及 Teradata SQL 中标题中的特定单词? [英] SQL: 4 transfers in different months within the last 6 months and specific words in title in Teradata SQL?

问题描述

我在 Teradata SQL 中有如下表:

而且我只需要选择这些行，其中:

And I need to select only these rows, where:

1. 在标题中有(在任何配置中，不区分大小写)字样:现金"或支付"
2. 在过去 6 个月内在不同月份至少进行过 4 次转移(2021-01-03 和 2021-07-03 之间)

1. In title has (in any configuration, not case sensitive) words: "cash" or "pay"
2. Have had at least 4 transfers in different months within the last 6 months (between 2021-01-03 and 2021-07-03)

因此，我只需要 ID = 111，因为此 ID 在过去 6 个月内至少有 4 个包含现金"的标题；或支付"(不同月份)

So, I need as a result only ID = 111 because this ID has within last 6 months at least 4 title included "cash" or "pay" (in different months)

ID
----
111

(简单来说，你在过去 6 个月内至少收到过 4 次工资转移——在不同的月份)

(to put it simply, you have received a salary transfer at least 4 times in the last 6 months -- in diferent month)

我知道我的示例表不适合这种情况，因为它只包含少量行，但我相信描述很清楚！

I am aware that my sample table is not ideal for this case, because it prestns to small amount of rows, but I believe that description is clear!

我需要在 Teradata SQL 上执行此操作，我该怎么做?

I need to do it on Teradata SQL, what can I do that ?

为了更精确:

1. 转移 ->是当标题有现金"时或支付"在任何配置中，不区分大小写，
2. ID 在表中不是唯一的，因为某些 ID 可以接收传输例如 5 次，
3. 构建表格，您有工人的付款清单，并且您想找到他们在过去 6 个月内至少收到 4 次转账(但每次转账都在不同的月份)

推荐答案

select id
from tab
      -- title has (in any configuration, not case sensitive) words: "cash" or "pay"
where title like any ('%cash%', '%pay%')
      -- last 6 months
  and date between add_months(current_date, -6) and current_date
group by id
       -- at least 4 transfers in different months
having count(distinct trunc(date, 'mon')) >= 4

除非 title 被定义为 CASESPECIFIC 或者您运行 ANSI 模式会话字符串比较在默认情况下不区分大小写.

Unless title is defined CASESPECIFIC or you run an ANSI-mode session string comparisons are case-insensitive by default.

这篇关于SQL:过去 6 个月内不同月份的 4 次传输以及 Teradata SQL 中标题中的特定单词?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！