如何在Amazon Redshift中使用垂直列获取记录 [英] How to fetch records using vertical columns in Amazon Redshift

问题描述

我需要获取两列中给出的日期范围之间的记录.

I need to fetch records between the date range given in two columns.

我的表格结构如下:

CREATE TABLE shared
(
   id         integer         NOT NULL,
   product          varchar(50)     NOT NULL,
   parent_id  integer         NOT NULL,
   key_code         varchar(100)    NOT NULL,
   key_value        varchar(8000)
);

INSERT INTO shared
    (`id`, `product`, `parent_id`, 'key_code', 'key_value')
VALUES
    (1, 'a',1, 'start_date','1/7/2011'),
    (2, 'a', 1,'end_date','15/7/2011'),
    (3, 'a',1, 'type','Promotion'),
    (4, 'a',1,'plan', 'new'),
    (5, 'a',5, 'start_date','11/8/2012'),
    (6, 'a', 5,'end_date','15/8/2012'),
    (7, 'a',5, 'type','Promotion'),
    (8, 'a',5,'plan', 'new'),
    (9, 'b',9, 'start_date','15/09/2015'),
    (10, 'b', 9,'end_date','15/09/2016'),
    (11, 'b',9, 'type','Promotion'),
    (12, 'b',9,'plan', 'new'),
;

现在我想获取开始日期> ='1/7/2011'到start_date< ='15/8/2012'之间的所有记录，其中product ='a'.

Now i want to fetch all the records between start date>='1/7/2011' to start_date<='15/8/2012' where product='a'.

我尝试过的查询是:

SELECT 
    *
FROM
    (SELECT   parent_id,
         product,
          MIN(CASE WHEN key_code = 'key_code' THEN key_value ELSE 'n/a' END) AS key_code,
          MIN(CASE WHEN key_code = 'start_date' THEN key_value ELSE 'n/a' END) AS start_date,
          MIN(CASE WHEN key_code = 'end_date' THEN key_value ELSE 'n/a' END) AS end_date,
          MIN(CASE WHEN key_code = 'type' THEN key_value ELSE 'n/a' END) AS type,
          MIN(CASE WHEN key_code = 'plan' THEN key_value ELSE 'n/a' END) AS plan,
FROM     shared
GROUP BY parent_id,
         product
ORDER BY parent_id) comp
WHERE
    start_date>= '01/12/2011'
        AND start_date <= '02/17/2011' and product='a';

我现在正在获取记录，更改了日期格式.

I am getting records now, changed the date format.

但是有什么方法可以优化此查询.?这样，当记录为数字时，将需要一些时间来执行.

But is there any way to optimize this query.? Like this will take time to execute when records are in numbers.

推荐答案

您的日期不是MySQL格式，因此必须进行转换才能进行比较

Your dates have not the MySQL format, so they have to be converted,to be compared

使用MIN和MAX以及您的'n/a，最好在内部Select中使用NULL，如果在外部，则将值n/a赋值为n/a，如果该值为NULL，则否则为n/qa如果有数据.

With the MIN and MAX and your 'n/a, it is better to use NULL in the inner Select and in the outer assign the Value n/a if the value is NULL, ot or else you get n/qa even if there is data.

SELECT 
    *
FROM
    (SELECT 
        parent_id,
            product,
            MIN(CASE WHEN key_code = 'start_date' THEN key_value ELSE 'n/a' END) AS start_date,
            MIN(CASE WHEN key_code = 'end_date' THEN key_value ELSE 'n/a' END) AS end_date,
            MAX(CASE WHEN key_code = 'type' THEN key_value ELSE 'n/a' END) AS type,
            MAX(CASE WHEN key_code = 'plan' THEN key_value ELSE 'n/a' END) AS plan
    FROM
        shared
    GROUP BY parent_id , product
    ORDER BY parent_id) comp
WHERE
    TO_DATE(start_date, 'DD/MM/YYYY') >= '2011-01-12'
        AND TO_DATE(start_date, 'DD/MM/YYYY') <= '2011-07-17'
        AND product = 'a'
;

这会给你z

 parent_id  product  start_date  end_date   type        plan
 1          a        1/7/2011    15/7/2011  Promotion   new

因此STR:TO:DATE在AWS中不存在，但它存在

So STR:TO:DATE doesn't exist in AWS but it EXISTS a TO_Date Instead of IF you maust do A CASe when like in your query

这篇关于如何在Amazon Redshift中使用垂直列获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！