Skip to content

Instantly share code, notes, and snippets.

@jdwyah
Created January 22, 2012 01:27
Show Gist options
  • Save jdwyah/1654916 to your computer and use it in GitHub Desktop.
Save jdwyah/1654916 to your computer and use it in GitHub Desktop.
Similarity SQL
WITH
target as (
select * from users where id = 12345
),
similarities as (
select
users.id as user_id,
10 * array_upper(users.condition_ids & target.condition_ids,1) / array_upper(users.condition_ids | target.condition_ids,1) as comorbidity_match,
15 * case when users.condition_ids[1] = target.condition_ids[1] then 1 else 0 end as primary_condition_match,
5 * case when users.birth_date is null then 0 else float8larger(float8(0), 40 - (abs(extract(years from age(users.birth_date, target.birth_date))))) / 40 end as age_match,
5 * case when users.sex = target.sex and users.sex is not null then 1 else 0 end as sex_match,
10 * (greatest(0,90 - date_part('days', (now() - users.last_activity_time)))) / 90 as activity_match,
2 * case when users.lat is null then 0 else .001 * float8larger(float8(0), 1000 - sqrt(pow(69.1 * (users.lat - target.lat),2) + pow(53.0 * (users.lng - target.lng),2))) end as distance_match
from users, target
where
users.verified is true and users.deleted is false
and users.role_id = 1
and users.deceased_date is null
and users.id <> 12345
),
matches as (
select *,
(0 + comorbidity_match+primary_condition_match+age_match+sex_match+activity_match+distance_match) as total_sum
from similarities
)
select * from matches
order by total_sum desc
limit 100
offset 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment