насколько очков новость отстает от предыдущей в рейтинге
SELECT
id,
section,
header,
score,
row_number() OVER w AS rating,
lag(score) OVER w - score AS score_lag
FROM news
WINDOW w AS (ORDER BY score DESC)
ORDER BY score desc;
id | section | header | score | rating | score_lag
----+---------+-----------+-------+--------+-----------
6 | 2 | Заголовок | 95 | 1 |
3 | 4 | Заголовок | 79 | 2 | 16
8 | 3 | Заголовок | 36 | 3 | 43
4 | 3 | Заголовок | 36 | 4 | 0
5 | 2 | Заголовок | 34 | 5 | 2
7 | 4 | Заголовок | 26 | 6 | 8
1 | 2 | Заголовок | 23 | 7 | 3
2 | 1 | Заголовок | 6 | 8 | 17
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
SELECT osm_id, highway, name, tags, ST_DISTANCE(way, point) AS Distance
FROM public.planet_osm_line
INNER JOIN (SELECT ST_TRANSFORM( ST_SetSRID(ST_POINT(48.323, 54.263), 4326), 900913) AS point ) AS p
ON ST_DWithin(point, way, 200)
WHERE highway IS NOT NULL AND name IS NOT NULL
ORDER BY Distance
LIMIT 1;