Compare values in duplicate tables-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Compare values in duplicate tables

P粉811329034 2023-09-13 08:52:32

471

I have a query that generates a table of duplicate records from a larger data set, and I would like to be able to perform further analysis on this output data.

Below is an example that can be used.

I would like to be able to identify, for each property, instances where the property's oldest rating was below 50 but the latest rating is now greater than 70.

In the example below, only ratings related to attribute 17 meet this condition, so the row containing Reference jygh will be extracted.

refer to	Attribute address	date	score
Adev	Attribute 12	2022-12-08	70
Pout	Attribute 12	2022-12-16	90
mhef	Properties 17	2023-01-02	25
jygh	Properties 17	2023-01-09	70
boy	Property 22	2022-10-05	85
qepl	Property 22	2022-10-25	28

This is a specialized analysis of change over time, so a max/min comparison of ratings alone is not sufficient.

Edit: I edited the data example to show the rating value decreasing, which does not meet the criteria.

P粉811329034

reply all (2)

P粉1419251812023-09-14 07:27:06 2 floor

This method only works if the date is not taken into account:

select PROPERTYADDRESS from TABLE1 where RATING <= 50 and PROPERTYADDRESS in ( select PROPERTYADDRESS from TABLE1 where RATING >= 70 );

Check it out here:https://dbfiddle.uk/6yoRNP74

Like+0

Add Reply

P粉0685109912023-09-14 00:56:13 1 floor

This will consider the oldest and newest rates:

SELECT mt.PROPERTYADDRESS FROM TABLE1 mt INNER JOIN ( SELECT PROPERTYADDRESS, MIN(Date) AS MinDate FROM TABLE1 GROUP BY PROPERTYADDRESS ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MinDate WHERE RATING <= 50 AND mt.PROPERTYADDRESS in ( SELECT mt.PROPERTYADDRESS FROM TABLE1 mt INNER JOIN ( SELECT PROPERTYADDRESS, MAX(Date) AS MaxDate FROM TABLE1 GROUP BY PROPERTYADDRESS ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MaxDate WHERE RATING >= 70 );

Check here:https://dbfiddle.uk/XzEIlvKc

Like+0

Add Reply