时间戳

首页 > 解决方案 > Snowflake - 日期表过滤问题

sql - Snowflake - 日期表过滤问题

问题描述

我创建了一个查询，它根据开始和结束日期生成一组日期。

一切正常，但是当我尝试过滤基础表以仅生成我的范围之间的记录时，它仍然会产生所有基础日期。

我试过使用 between，使用 to_date() 将变量更改为日期，没有任何效果。

SET DATE_START_RANGE = '2018-12-1';

SET DATE_END_RANGE = CASE 
                    WHEN CURRENT_DATE = LAST_DAY(CURRENT_DATE) THEN CURRENT_DATE
                    ELSE LAST_DAY(DATEADD(MONTH,-1, CURRENT_DATE))
                    END;
                   


SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
      SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
      FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
      WHERE 1 =1 
      
     ) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE  -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE  -- this filter is not being picked up
--AND DATES BETWEEN $DATE_START_RANGE AND  $DATE_END_RANGE  -- this filter is not being picked up
AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%'

有任何想法吗？

标签： sqlsnowflake-cloud-data-platform

解决方案

OR条件是AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%'问题：

SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
      SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
      FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
      WHERE 1 =1 
      
     ) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE  -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE  -- this filter is not being picked up
AND (DATES LIKE '____-06-%' OR DATES LIKE '____-12-%')  
    -- here should be brackets around condition

使用LIKE ANY (..., ...)语法的更简洁的方式：

SELECT DATES
FROM (
 SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01')::DATE AS DATES
 FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
     ) LIST_OF_DATES
WHERE DATES BETWEEN $DATE_START_RANGE AND $DATE_END_RANGE 
AND DATES LIKE ANY ('____-06-%', '____-12-%');

推荐阅读