Mercurial > gemma
annotate schema/countries.sql @ 4449:ce884af9f42f
Fix forecast accuracy values and improve performance
Forecast accuracy should be based only on the most recent predictions.
Generally consider measurements and predictions older than 14 days as
outdated and irrelevant. This also implies a performance gain, since it
saves a full scan of gauge_measurements and the extra index for this
purpose.
Adding an extra subquery level for calculation of the forecast accuracy per
row instead of the function call as a FROM item saves a (nested loop)
join, which is another performance gain.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 20 Sep 2019 13:22:45 +0200 |
parents | 5e38667f740c |
children |
rev | line source |
---|---|
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 -- Author(s): |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 -- * Sascha Wilde <sascha.wilde@intevation.de> |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 -- * Tom Gottfried <tom@intevation.de> |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 -- Setup Country Codes |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 COPY countries (country_code) FROM stdin; |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 AT |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 BG |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 HR |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 HU |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 RO |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 RS |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 SK |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 \. |