87 lines
5.1 KiB
PL/PgSQL
87 lines
5.1 KiB
PL/PgSQL
-- fix issue with current_scores; only combine user data after the median score has been selected
|
|
-- fix issue with both sms_index/ams_index; put DISTINCT on the initial insert into the ams_users_tmp/sms_users_tmp, which is necessary after current_scores change
|
|
DROP VIEW current_scores;
|
|
DROP VIEW current_network_scores;
|
|
CREATE OR REPLACE VIEW current_network_scores AS
|
|
|
|
WITH recent_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT *, row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY scores.created_at DESC) AS rownum FROM scores) tmp WHERE rownum < 6
|
|
|
|
), ranked_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT percent_rank() over (PARTITION BY alocidispid, blocidispid ORDER BY score ASC) AS pc, * FROM recent_scores) tmp WHERE pc <= .5 ORDER BY pc DESC
|
|
|
|
), median_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT * , row_number() OVER (PARTITION BY alocidispid, blocidispid, scorer ORDER BY score DESC) AS pc2 FROM ranked_scores) tmp where pc2 < 2
|
|
|
|
)
|
|
SELECT alocidispid, blocidispid, score, created_at, scorer FROM median_scores;
|
|
|
|
CREATE OR REPLACE VIEW current_scores AS
|
|
|
|
SELECT current_network_scores.*, a_users.id as a_userid, b_users.id as b_userid, (COALESCE(a_users.last_jam_audio_latency, 13) + COALESCE(b_users.last_jam_audio_latency, 13) + score) AS full_score, a_users.last_jam_audio_latency AS a_audio_latency, b_users.last_jam_audio_latency AS b_audio_latency
|
|
FROM current_network_scores
|
|
INNER JOIN users as a_users ON a_users.last_jam_locidispid = current_network_scores.alocidispid
|
|
INNER JOIN users as b_users ON b_users.last_jam_locidispid = current_network_scores.blocidispid
|
|
ORDER BY full_score ASC;
|
|
|
|
|
|
CREATE OR REPLACE VIEW nondirected_network_scores AS
|
|
WITH recent_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at FROM (SELECT *, row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY scores.created_at DESC) AS rownum FROM scores) tmp WHERE rownum < 6 AND scorer = 0
|
|
|
|
), ranked_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at FROM (SELECT percent_rank() over (PARTITION BY alocidispid, blocidispid ORDER BY score ASC) AS pc, * FROM recent_scores) tmp WHERE pc <= .5 ORDER BY pc DESC
|
|
|
|
), median_scores AS (
|
|
|
|
SELECT alocidispid, blocidispid, score, created_at FROM (SELECT * , row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY score DESC) AS pc2 FROM ranked_scores) tmp WHERE pc2 < 2
|
|
|
|
), forward_scores AS (
|
|
|
|
SELECT alocidispid AS from_location, blocidispid AS to_location, score, created_at FROM median_scores
|
|
|
|
), backward_scores AS (
|
|
|
|
SELECT blocidispid AS from_location, alocidispid AS to_location, score, created_at FROM median_scores
|
|
|
|
), merged_directions AS (
|
|
|
|
SELECT from_location, to_location, score, created_at FROM forward_scores UNION SELECT from_location, to_location, score, created_at FROM backward_scores
|
|
|
|
)
|
|
SELECT from_location AS alocidispid, to_location AS blocidispid, score, created_at FROM (SELECT *, row_number() OVER (PARTITION BY from_location, to_location ORDER BY created_at DESC) AS row FROM merged_directions) tmp WHERE row < 2;
|
|
|
|
|
|
CREATE OR REPLACE VIEW nondirected_scores AS
|
|
|
|
SELECT nondirected_network_scores.*, a_users.id as a_userid, b_users.id as b_userid, (COALESCE(a_users.last_jam_audio_latency, 13) + COALESCE(b_users.last_jam_audio_latency, 13) + score) AS full_score, a_users.last_jam_audio_latency AS a_audio_latency, b_users.last_jam_audio_latency AS b_audio_latency
|
|
FROM nondirected_network_scores
|
|
INNER JOIN users as a_users ON a_users.last_jam_locidispid = nondirected_network_scores.alocidispid
|
|
INNER JOIN users as b_users ON b_users.last_jam_locidispid = nondirected_network_scores.blocidispid
|
|
ORDER BY full_score ASC;
|
|
|
|
|
|
CREATE VIEW most_recent_scores AS SELECT * FROM scores s WHERE score_dt = (SELECT max(score_dt) FROM scores s0 WHERE s0.alocidispid = s.alocidispid AND s0.blocidispid = s.blocidispid);
|
|
|
|
DROP FUNCTION get_work (mylocidispid BIGINT, myaddr BIGINT);
|
|
CREATE FUNCTION get_work (mylocidispid BIGINT, myaddr BIGINT) RETURNS TABLE (client_id VARCHAR(64)) ROWS 5 VOLATILE AS $$
|
|
BEGIN
|
|
CREATE TEMPORARY TABLE foo (locidispid BIGINT, locid INT);
|
|
INSERT INTO foo SELECT DISTINCT locidispid, locidispid/1000000 FROM connections WHERE client_type = 'client';
|
|
DELETE FROM foo WHERE locidispid IN (SELECT DISTINCT blocidispid FROM most_recent_scores WHERE alocidispid = mylocidispid AND (current_timestamp - score_dt) < INTERVAL '24 hours');
|
|
DELETE FROM foo WHERE locid NOT IN (SELECT locid FROM geoiplocations WHERE geog && st_buffer((SELECT geog FROM geoiplocations WHERE locid = mylocidispid/1000000), 4023360));
|
|
CREATE TEMPORARY TABLE bar (client_id VARCHAR(64), locidispid BIGINT, r DOUBLE PRECISION);
|
|
INSERT INTO bar SELECT l.client_id, l.locidispid, random() FROM connections l, foo f WHERE l.locidispid = f.locidispid AND l.client_type = 'client' AND addr != myaddr;
|
|
DROP TABLE foo;
|
|
DELETE FROM bar b WHERE r != (SELECT MAX(r) FROM bar b0 WHERE b0.locidispid = b.locidispid);
|
|
RETURN QUERY SELECT b.client_id FROM bar b ORDER BY r LIMIT 5;
|
|
DROP TABLE bar;
|
|
RETURN;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|