Hello! I ask for help for a beginner, there are 3 tables: two of them are with data on goods (at the store and area levels), the third one connects the first two with codes (store and area)

DB tables (zone; loc; zone_loc)

a table that links the above listed by region / store codes (one store can be located only in one region, but there can be several stores in one region) (zone_loc)

There are 2 questions. First: you need to write a request showing the price for goods located in the first (zone = 1) area at the beginning and end of the period (June 1, 2015 through July 20, 2016)

SELECT item, ac_date, price FROM zone WHERE ac_date <= '2015.6.1' AND zone_id = 1 UNION SELECT item, ac_date, price FROM loc a JOIN zone_loc b ON a.loc_id = b.loc_id WHERE a.ac_date <= '2015.6.1' AND b.zone_id = 1 UNION SELECT item, ac_date, price FROM zone WHERE ac_date = '2016.7.20' AND zone_id = 1 UNION SELECT item, ac_date, price FROM loc a JOIN zone_loc b ON a.loc = b.loc WHERE a.ac_date = '2016.7.20' AND b.zone_id = 1;

the problem is that not all products changed prices right on the last day of the period (2016.7.20), so when changing from

 WHERE ac_date = '2016.7.20'

on

 WHERE ac_date BETWEEN '2015.6.2' AND'2016.7.20'

intermediate values ​​are included in the sample, which by the end of the period have changed their price. How to change the request so that in the sample at the end of the period only products with final prices are displayed? And how can I change a query without using union?

There is one more question, you need to create such a request, which would show the price of goods at the store level (table loc), which is not at the level of the area to which the store is attached. Here, as I understand it, there must be a correspondence by dates (among other things, the product id, zone to loc). According to this task, I don’t have any good practices at all, there is a thought that this should be with the store goods table (loc) through the communication table (zone_loc) with the area goods table (zone), and then through the EXISTS operator to find those records that are not in the zone table

 SELECT a.item, a.loc_id, c.zone_id, a.ac_date, a.price FROM loc a JOIN zone_loc b ON a.loc_id = b.loc_id JOIN zone c ON b.zone_id = c.zone_id WHERE a.item = c.item AND a.ac_date = c.ac_date;

But it’s not at all, and I don’t know how to continue using EXISTS (if I use it here at all) and come to the right decision. I would be grateful for any help in these matters!

  • one
    Why do you have in the tags are several DBMS. The sql-server and postrgesql syntax is different. especially when it comes to non-standard queries. Specify exactly your DBMS. In addition, for the solution it is necessary to give a sample of input data in text form and show what result is expected to be obtained, it is difficult to understand from your text description what exactly is required - Mike
  • On the vskidku, on the first question, the functions first_value and last_value with order by in over () by date and partition by item - Mike

1 answer 1

Very incomprehensible question. But I will try to write at least something.

For starters, you don't need a third table. A one-to-many relationship is made without an intermediate table. It is needed for many-to-many. Just add one more field in the store table - the ID of the location where the store is located.

About your first question. If you need a price for the beginning of the period - just make a request for this price: limit the search area to the desired dates, sort the selection and take the first record. For the last date of the period, do the same, but sort the sample in the opposite direction.

In the example below, there is no grouping by product, you need to add it (on the model, I started only one product for tests).

 ( SELECT 'цена на начало периода' price_type, item, ac_date, price FROM zone WHERE ac_date BETWEEN '2015-06-01' AND '2016-07-20' ORDER BY ac_date ASC LIMIT 1 ) UNION ( SELECT 'цена к концу периода' price_type, item, ac_date, price FROM zone WHERE ac_date BETWEEN '2015-06-01' AND '2016-07-20' ORDER BY ac_date DESC LIMIT 1 )

It turns out something like this:

 price_type item ac_date price ----------------------- ----- ---------- ------ цена на начало периода 1 2015-06-01 100 цена к концу периода 1 2016-07-20 200

Tested on data:

 CREATE TEMPORARY TABLE zone(item INT, zone_id INT, ac_date DATE, price INT) -- цена до периода SELECT 1 item, 1 zone_id, '2015-05-01' ac_date, 500 price UNION -- начало периода SELECT 1 item, 1 zone_id, '2015-06-01' ac_date, 100 price UNION -- промежуточные колебания цены SELECT 1 item, 1 zone_id, '2015-06-02' ac_date, 150 price UNION SELECT 1 item, 1 zone_id, '2015-06-03' ac_date, 250 price UNION SELECT 1 item, 1 zone_id, '2015-06-04' ac_date, 50 price UNION SELECT 1 item, 1 zone_id, '2015-06-05' ac_date, 120 price UNION -- конец периода SELECT 1 item, 1 zone_id, '2016-07-20' ac_date, 200 price UNION -- цена после периода SELECT 1 item, 1 zone_id, '2016-07-25' ac_date, 500 price
  • Thank! I will try to implement the proposed by you. Only as I understand it, you need to streamline changes in the prices of goods and take the latest current prices before the beginning of the period (including the first day of 2015-06-01, because the last price change can be before the beginning of the period), and then arrange them with DESC as in the second SELECT in your example. And then take the period of the billing period from 2015-06-02 to 2016-07-20 inclusive in the same way with DESC. The question immediately arises, according to which (how) column to group, if one product (with the same id) has several entries with different dates and prices - nival
  • If you need a price at the time of entry into the reporting period, then where ac_date < 'начало_периода' order by ac_date desc limit 1 . At the time of exit from the period, simply change the date to the date of the end of the period. Grouping depends on the price you need. From the question it is not at all clear what problem you are trying to solve and what are the business requirements for it. - Lexx918