CODEWITHSUNDEEP
phpsqlpostgresqlpostgresql-9.6
Zoltán Fekete
Zoltán Fekete

Reputation: 604

Rate calculation in SQL

right now, I really have no idea how could I solve this.

I have users who can deal with a different types of tasks.

These tasks have many properties. I chose just 4 property for simplicity.

If I assign a task to a user, I have to give a rate to this assignment. This rate will determine how much I have to pay for him, when he finished his task.

I have the following table. These id fields are nullable (except user_id), and all of them are foreign keys.

|-#-|-user_id-|-type_id-|-task_id-|-source_id-|-target_id-|--rate--|
| 1 |   12    |   NULL  |    1    |     1     |     2     | 0.0022 |
| 2 |   12    |   NULL  |    2    |     1     |     2     | 0.0101 |
| 3 |   12    |   NULL  |    1    |     2     |     1     | 0.0200 |
| 4 |   12    |   NULL  |    2    |     2     |     1     | 0.1011 |
| 5 |   12    |    1    |   NULL  |    NULL   |    NULL   | 0.0750 |
| 6 |   12    |    2    |    1    |    NULL   |    NULL   | 0.0520 |
| 7 |   12    |   NULL  |    1    |    NULL   |    NULL   | 0.9100 |

Of course there are rates for other users too.

There is now weights, just left to right order (from column type_id).

But: it is important that: To the left from the first column match, have to be all equal to the assigned task values or NULL values. To the right have to be equal to the assigned task values or NULL

Example 1

user_id: 12
type_id: 3
task_id: 2
source_id: 1
target_id: 2

#2 - 0.0101 rate should be selected.

Example 2

user_id: 12
type_id: 1
task_id: 2
source_id: 3
target_id: 4

#5 - 0.0750 rate should be selected.

Example 3

user_id: 12
type_id: 2
task_id: 1
source_id: 2
target_id: 3

#6 - 0.0520 rate should be selected.

So far, I'm ok with this. But I can't solve the following:

Example 4

user_id: 12
type_id: 2
task_id: 2
source_id: 2
target_id: 3

NONE should be selected.

Example 5

user_id: 12
type_id: 3
task_id: 1
source_id: 2
target_id: 3

NONE should be selected.

Upvotes: 1

Views: 743

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I think you want order by and fetch first row:

select t.*
from t
where user_id = 12 and
      (type_id = 3 or type_id is null) and
      (task_id = 2 or task_id is null) and
      (source_id = 1 or source_id is null) and
      (target_id = 2 or target_id is null)
order by ( (type_id is not null)::int +
           (task_id is not null)::int +
           (source_id is not null)::int +
           (target_id is not null)::int
         ) desc
fetch first 1 row only;

This returns the row that matches your conditions, but with the fewest NULL values (that is, the most specific row).

EDIT:

If you need to match them in order, then the where clause looks like:

order by (type_id is not null)::int desc,
         (task_id is not null)::int desc,
         (source_id is not null)::int desc,
         (target_id is not null)::int desc

Upvotes: 1

Related Questions