如何在snowflake的where子句中编写case语句

aiqt4smr  于 2021-08-13  发布在  Java
关注(0)|答案(3)|浏览(426)

我试图重写一段最初为vertica编写的sql代码。vertica有 AGE_IN_YEARS() 内置的功能,这是伟大的获得某人的年龄。但是,现在,我需要想办法让它在雪花中工作,我做到了。
替换的等价语句 AGE_IN_YEARS(DateOfBirth) 在雪花中可以是:

  1. case when dateadd(year, datediff(years, DateOfBirth, CURRENT_DATE), DateOfBirth) > CURRENT_DATE
  2. then datediff(years, DateOfBirth, CURRENT_DATE) -1
  3. else datediff(years, DateOfBirth, CURRENT_DATE)
  4. end as AGE

问题是我必须在where子句中使用这个。最初的vertica代码是:

  1. SELECT name, dob,
  2. (SELECT AgeRangeCode
  3. FROM AgeRangeTable
  4. WHERE AGE_IN_YEARS(DateOfBirth) BETWEEN MinAge AND MaxAge) AS AgeRangeCode,
  5. CURRENT_DATE as TODAY
  6. from MemberInfoTable

如果我加上 AGE_IN_YEARS() 替换上述查询,我得到
sql编译错误:无法计算不支持的子查询类型
我该如何在where子句中写这个例子呢

cig3rfwq

cig3rfwq1#

问题是我必须在where子句中使用这个。
把年龄加上去怎么样 dateofbirth 相反呢?

  1. where dateadd(year, minage, dateofbirth) <= current_date and
  2. dateadd(year, maxage + 1, dateofbirth) > current_date
jaql4c8m

jaql4c8m2#

不使用函数计算年龄,只需加入您喜欢的数据即可:

  1. with age_range_table as (
  2. select * from values (0,16, 'A'),(16,18, 'B'),(18,1000, 'C') v(min_age, max_age, age_range_code)
  3. ), member_info_table as (
  4. select name, dob::date as dob from values ('user_80s', '1980-01-01'),('user_90s', '1990-01-01'),('user_10s', '2010-01-01') v(name, dob)
  5. )
  6. select m.name,
  7. m.dob,
  8. a.age_range_code
  9. from member_info_table AS m
  10. join age_range_table AS a
  11. ON m.dob < dateadd('years',-a.min_age, current_date)
  12. AND m.dob >= dateadd('years',-a.max_age, current_date);

给:

  1. NAME DOB AGE_RANGE_CODE
  2. user_80s 1980-01-01 C
  3. user_90s 1990-01-01 C
  4. user_10s 2010-01-01 A

但在where子句中进行函数调用有点恶心。。

  1. with age_range_table as (
  2. select *
  3. from values
  4. (0,16, 'A'),
  5. (16,18, 'B'),
  6. (18,1000, 'C')
  7. v(min_age, max_age, age_range_code)
  8. ), member_info_table as (
  9. select name, dob::date as dob
  10. from values
  11. ('user_80s', '1980-01-01'),
  12. ('user_90s', '1990-01-01'),
  13. ('user_10s', '2010-01-01')
  14. v(name, dob)
  15. ), today_age_range_table as (
  16. select age_range_code
  17. ,dateadd('years',-min_age, current_date) as range_end_date
  18. ,dateadd('years',-max_age, current_date) as range_start_date
  19. from age_range_table
  20. )
  21. select m.name,
  22. m.dob,
  23. a.age_range_code
  24. from member_info_table AS m
  25. join today_age_range_table AS a
  26. ON m.dob < a.range_end_date
  27. AND m.dob >= a.range_start_date;

贷方注解:这是与gordon的解决方案相同的解决方案,但它是显式显示的。并且范围逻辑更改为在cte verse中更改所有行的user.dob一次范围时间。基于这样的假设,你拥有的用户多于范围,如果两者都低于1000,你就没有问题了。

展开查看全部
kqhtkvqz

kqhtkvqz3#

如果您在agerange表中有age列,并且您希望将每个客户/参与者的年龄与之进行比较,并且还希望在age range表的min和max范围内进行比较,那么您可以通过两种方式简单地进行比较。

  1. SELECT name, dob,
  2. [AgeRangeCode] = (SELECT AgeRangeCode FROM AgeRangeTable WHERE DATEDIFF(Year,mi.dob,CURRENT_DATE) = mi.age
  3. AND DATEDIFF(Year,mi.dob,CURRENT_DATE) BETWEEN MinAge AND MaxAge),
  4. CURRENT_DATE as TODAY
  5. from MemberInfoTable mi

或者像下面这样简单的连接

  1. SELECT mi.name, mi.dob, art.AgeRangeCode,
  2. CURRENT_DATE as TODAY
  3. FROM MemberInfoTable mi
  4. JOIN AgeRangeTable art ON art.age = DATEDIFF(Year,mi.dob,CURRENT_DATE)
  5. WHERE DATEDIFF(Year,mi.dob,CURRENT_DATE) BETWEEN art.MinAge AND art.MaxAge

相关问题