值在一个数字范围内的组中存在（SQL）

Question

我想知道给定范围的大小（假设3：X，Y，Z）有多少模型（比如说T恤）我有一个给定的日期和特定的商店（比方说3：A，B，C）库存。

其中：

X = between 40 and 50 
Y = between 30 and 60 
Z = between 20 and 70 

最后的结果将是这个样子（但有不少成果）：

Date | Store | Model | Availability X | Availability Y | Availability Z 
02/26 | A  | shirt | Yes   | Yes   | No 
02/26 | B  | shirt | Yes   | No    | No 
02/26 | C  | shirt | Yes   | Yes   | Yes 

的可用性意味着我必须在股票之间的所有尺寸给定的尺寸范围。

我仍然在想办法做到这一点。我的表，现在这样设计的（一些说明性信息）：

表 “设置”

 id | name  | initial_value | final_value 
    1 | X  | 40   | 50 
    2 | Y  | 30   | 60 
    3 | Z  | 20   | 70 

表 “项目”

 id | date  | store | model  | size | in_stock 
    1 | 02/26 | A  | shirt  | 40  | 1 
    2 | 02/26 | A  | shirt  | 50  | 2 
    3 | 02/26 | A  | shirt  | 30  | 0 
    4 | 02/26 | B  | shirt  | 30  | 1 

我明白任何帮助！谢谢。

Answer 1

也许你可以尝试这样的事：

SELECT  date, 
      store, 
      model, 
      SUM(
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 1) AND (SELECT final_value FROM Table "sets" WHERE id = 1) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as "Availability X", 
      SUM(
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 2) AND (SELECT final_value FROM Table "sets" WHERE id = 2) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as "Availability Y", 
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 3) AND (SELECT final_value FROM Table "sets" WHERE id = 3) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as "Availability Z" 


FROM  Table "items" 

WHERE  date > '02/26/2016' 
      AND 
      date < '02/26/2016' 
      AND 
      Model = 'shirt' 

GROUP BY date, store, model 

我认为这会给你你后的信息，但如果你想输出完全一样，你拥有它，那么你可以换另一种情况声明围绕每个可靠性case语句，或使用CTE象下面这样：

WITH 
data AS 
(
SELECT  date, 
      store, 
      model, 
      SUM(
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 1) AND (SELECT final_value FROM Table "sets" WHERE id = 1) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as availability_x, 
      SUM(
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 2) AND (SELECT final_value FROM Table "sets" WHERE id = 2) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as availability_y, 
      SUM(
       CASE 
        WHEN size BETWEEN (SELECT initial_value FROM Table "sets" WHERE id = 3) AND (SELECT final_value FROM Table "sets" WHERE id = 3) 
         THEN in_stock 
        ELSE 0 
       END 
      )  as availability_z 


FROM  Table "items" 

WHERE  date > '02/26/2016' 
      AND 
      date < '02/26/2016' 
      AND 
      Model = 'shirt' 

GROUP BY date, store, model 
) 

SELECT  date, store, model, 
      CASE 
       WHEN availability_x > 0 THEN "Yes" 
       ELSE "No" 
      END as "Availability X", 
      CASE 
       WHEN availability_y > 0 THEN "Yes" 
       ELSE "No" 
      END as "Availability Y", 
      CASE 
       WHEN availability_z > 0 THEN "Yes" 
       ELSE "No" 
      END as "Availability Z" 

FROM  data 

Answer 2

这是SQL Server的输出，我不知道PostgreSQL的。

- 创建SETS

create table dbo.test_sets 
(
    id int not null, 
    name varchar(255), 
    initial_value int not null default (0), 
    final_value int not null default(0) 
) 
go 

insert into dbo.test_sets(id, name, initial_value, final_value) 
values (1, 'X', 40, 50) 

insert into dbo.test_sets(id, name, initial_value, final_value) 
values (2, 'Y', 30, 60) 

insert into dbo.test_sets(id, name, initial_value, final_value) 
values (3, 'Z', 20, 70) 
go 

- 创建ITEMS

create table dbo.test_items 
(
    id int not null, 
    [date] date, 
    store varchar(255) not null, 
    model varchar(255) not null, 
    size int not null default (0), 
    in_stock int not null default(0) 
) 
go 

insert into dbo.test_items(id, [date], store, model, size, in_stock) 
values (1, '02/26/2016', 'A', 'shirt', 40, 1) 

insert into dbo.test_items(id, [date], store, model, size, in_stock) 
values (2, '02/26/2016', 'A', 'shirt', 50, 2) 

insert into dbo.test_items(id, [date], store, model, size, in_stock) 
values (3, '02/26/2016', 'A', 'shirt', 30, 0) 

insert into dbo.test_items(id, [date], store, model, size, in_stock) 
values (4, '02/26/2016', 'B', 'shirt', 30, 1) 

insert into dbo.test_items(id, [date], store, model, size, in_stock) 
values (5, '02/26/2016', 'C', 'shirt', 80, 1) 
go 

- 创建NUMBERS LOOKUP

create table dbo.test_numbers 
(
    id int not null 
) 
go 

declare @first as int 
declare @step as int 
declare @last as int 
select @first = 1, @step = 1, @last = 100 

BEGIN TRANSACTION 
    WHILE(@first <= @last) 
    BEGIN 
     INSERT INTO dbo.test_numbers VALUES(@first) SET @first += @step 
    END 
COMMIT TRANSACTION 
go 

- 提供所需查询输出

;with unique_store_models as 
(
    select distinct store, model from dbo.test_items 
), 
set_sizes as 
(
    select ts.id, ts.name as size_group, tn.id as size 
    from 
     dbo.test_sets ts 
     inner join dbo.test_numbers tn on 
      tn.id between ts.initial_Value and ts.final_value 
), 
items_by_sizes_flat as 
(
    select 
     ti.[date], 
     usm.store, 
     usm.model, 
     ss.size_group, 
     ss.size, 
     ti.in_stock 
    from 
     unique_store_models usm 
     left outer join dbo.test_items ti on 
     ti.store = usm.store 
     and ti.model = usm.model 
     left outer join set_sizes ss on 
     ss.size = ti.size 
), 
items_by_sizes_pivoted as 
(
    select 
     * 
    from 
    (
     select 
      [date], 
      store, 
      model, 
      size_group, 
      --size, 
      in_stock 
     from 
      items_by_sizes_flat 
    ) as p 
    PIVOT 
    (
     count(in_stock) for size_group in ([X], [Y], [Z]) 
    ) as pv 
) 
select 
    [date], 
    store, 
    model, 
    case 
     when [X] > 0 then 'Yes' else 'No' 
    end as [Availability X], 
    case 
     when [Y] > 0 then 'Yes' else 'No' 
    end as [Availability Y], 
    case 
     when [Z] > 0 then 'Yes' else 'No' 
    end as [Availability Z] 
from 
    items_by_sizes_pivoted 

下面是上述输入的输出：

Answer 3

此典型的例子的crosstab()使用情况。

样本数据：

-- DDL and data 
CREATE TABLE items(
    id SERIAL PRIMARY KEY, 
    "Date" DATE, 
    store TEXT, 
    model TEXT, 
    size INTEGER, 
    in_stock INTEGER 
); 
INSERT INTO items VALUES 
    (1, '02/26/2016':: DATE, 'A', 'shirt', 40, 1), 
    (2, '02/26/2016':: DATE, 'A', 'shirt', 50, 2), 
    (3, '02/26/2016':: DATE, 'A', 'shirt', 30, 0), 
    (4, '02/26/2016':: DATE, 'B', 'shirt', 30, 1); 
CREATE TABLE sets(
    id SERIAL PRIMARY KEY, 
    name TEXT, 
    initial_value INTEGER, 
    final_value INTEGER 
); 
INSERT INTO sets VALUES 
    (1, 'X', 40, 50), 
    (2, 'Y', 30, 60), 
    (3, 'Z', 20, 70); 

为了选择我用int4range(start,end,inclusion)功能配置的尺寸范围包括。查询本身：

SELECT * FROM crosstab(
    'SELECT i.store,i."Date",i.model,s.name, 
    bool_or(CASE WHEN size_range @> i.size 
       THEN TRUE 
       ELSE FALSE 
      END) 
    FROM items i,sets s,int4range(s.initial_value, s.final_value, ''[)'') AS size_range 
    WHERE i.in_stock > 0 
    GROUP BY 1,2,3,4 
    ORDER BY 1,2', 
    'SELECT DISTINCT(name) FROM sets ORDER BY 1') 
AS output(store TEXT,"Date" DATE,model TEXT,"Availability X" BOOLEAN,"Availability Y" BOOLEAN,"Availability Z" BOOLEAN); 

结果：

store | Date | model | Availability X | Availability Y | Availability Z 
-------+------------+-------+----------------+----------------+---------------- 
A  | 2016-02-26 | shirt | t    | t    | t 
B  | 2016-02-26 | shirt | f    | t    | t 
(2 rows)