CODEWITHSUNDEEP
sqlfuzzy-logic
Paddy.M
Paddy.M

Reputation: 39

Fuzzy Logic lookup

The table

Hello, as per the attached image, we are trying to update a 1.7million row UK postcode table with insurance risk groups. There are several thousand New Postcodes with no groupNumber and these appear as NULL. Want to replace the NULLs with the value from the postcode in the row above. Believe we should be using some sort of fuzzy logic but need some help please. Thanks

Upvotes: 0

Views: 151

Answers (2)

Manuel Rodriguez
Manuel Rodriguez

Reputation: 782

Fuzzy logic is about writing rules in structured English. These rules can be understood by man and machine at the same time and are easily adjusted to further requirements. A rule for replacing the NULL postcode can be written in Forth:

OrigPostcode NULL = IF
  row_above OrigPostcode
  row_above groupnumber
THEN

The term “row_above” is a linguistic variable. And above means that the index is i-1 with 0.9 affiliation.

Instead of formulating the rule manually, it's also possible to use a technique called “Learning from demonstration”. That means a human operator is doing the copy&paste task first and the system will recognize the rule autonomously. This is usually done with neural networks and described in open access journals freely available in the Internet provided by predatory publishing groups.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In a query, you can do:

select t.*, 
       coalesce(groupnumber, lag(groupnumber) over (order by new_postcode)) as new_groupnumber
from t;

It is not clear if you want to actually change the data or just return the values in a query.

Upvotes: 1

Related Questions