Re: Table schema inhancement - Mailing list pgsql-general

On 04/12/2018 05:59 PM, hmidi slim wrote:
> Hi,
> I have these tables:
> price_per_occupation: id (integer), product_price_period_id(integer), 
> occupation_type(integer), price (numeric)
> 
> product_price_period; id(integer), product_id(integer), 
> is_monday(boolean), is_tuesday(boolean), is_wednesday(boolean), 
> is_thursday(boolean), is_friday(boolean), is_saturday(boolean), 
> is_sunday(boolean), price_period(daterange)
> 
> occupation_type: id(integer), name(varchar)
> 
> product: id(integer), name(varchar)
> 
> I run this query:
> /*select price_per_occupation.price, product_price_period.price_period, 
> occupation_type.name <http://occupation_type.name>
> */
> /*from price_per_occupation inner join product_price_period on 
> product_price_period.id <http://product_price_period.id>= 
> price_per_occupation.product_price_period_id
> */
> /*inner join occupation_type on occupation_type.id 
> <http://occupation_type.id> = price_per_occupation.occupation_type
> */
> /*inner join product on product.id <http://product.id> = 
> product_price_period.product_id
> */
> /*where product_price_period.price_period @> '[2018-07-22, 2018-07-23]'
> */
> /*and occupation_type.id <http://occupation_type.id> = 1*/
> 
> 
> This query returns all the products with an occupation_type = 1 and have 
> the period_price between 2018-07-22 and 2018-07-23.
> 
> However I need to verify if the boolean values verified.
> E.g if is_monday = true the date corresponding to Monday will be 
> eliminated from the period.
> if is_sunday = true and is_friday = true the dates corresponding to 
> Sunday and Friday will be eliminated from the period interval.
> If I will choose all the products in the interval 
> [2018-04-07,2018-04-14] and is_monday = true and is_thursday= true
> the date of monday is 09/04/2018 and date of friday is 13/04/2018.
> I have to get all products contained in [2018-04-07,2018-04-08] U 
> [2018-04-10, 2018-04-12] U [2018-04-14, 2018-04-14]
> 
> In order to get the date of truthy columns I should execute a function 
> which contains a query like that:
> 
> |select *
> |
> |from generate_series(date '2018-04-07', date '2018-04-14', interval '1 
> day') the day
> |
> |where extract ('dow', the_day) = 1
> 
> 
> |
> |I'm face many problems with this schema;
> |
> |I should verify the boolean values.
> |
> |I should extract the corresponding dates based to the values.
> |
> |I decompose the period into different periods then run the query to 
> fetch the products.
> 
> |
> |How can I enhance the query? or is there any proposition for the schema 
> table to enhance it?

My 2 cents eliminate the is_* fields and create a single field:

dow_verified integer[]

Then assuming non-iso week day number Sunday(0) --> Saturday(6) in the 
array enter the day of week(dow) numbers for verified days e.g.:

ARRAY[0, 1, 4]

for Sunday, Monday, Thursday.

Then to get the days that are not verified over a period something like:

WITH dp AS (
     SELECT
         extract('dow' FROM generate_series('04/01/18'::date,
                                            '04/14/18'::date, '1 day'))
             AS dow,
         generate_series('04/01/18'::date, '04/14/18'::date, '1 day')
             AS dt
)
SELECT
     dp.*
FROM
     dp
WHERE
     dp.dow NOT IN (
         (
             SELECT
                 *
             FROM
                 unnest(ARRAY [ 0, 1, 4 ]) AS dow_dt)
         )

ORDER BY
     dt;

dow |           dt
-----+------------------------
    2 | 2018-04-03 00:00:00-07
    3 | 2018-04-04 00:00:00-07
    5 | 2018-04-06 00:00:00-07
    6 | 2018-04-07 00:00:00-07
    2 | 2018-04-10 00:00:00-07
    3 | 2018-04-11 00:00:00-07
    5 | 2018-04-13 00:00:00-07
    6 | 2018-04-14 00:00:00-07
(8 rows)



> |
> ||
> ||
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com