So in your application you don't care about when a temperature was measured? Creating a table temps (patient_id, timestamp, temperature) with an index on (timestamp,temperature) would yield a fast way to access patients with elevated temperatures within a time frame.
Other than that, using triggers is probably the easiest way to go. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Donnerstag, 15. März 2018 18:34 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Indexing multiple values per row I'm wondering what the best way is to efficiently search for data values that can appear multiple times in a table row. SQLite indexes, even expression indexes, don't directly work for this because they obviously only index one value per row. Traditional relational-database design says to normalize the schema by storing the multiple values in separate rows, but what if the data to be indexed is JSON and you need to leave it in that form? For example, let's say I have a table like patient_id: 12345 temps: "[98.6, 99.1, 101.3, 100.0, 98.9]" and I want to run queries on temperature data, like 'patients who've had a temperature above 101'. And I need better than O(n) performance. In the past my project used map/reduce to support this, essentially implementing its own index system on top of SQLite tables. In this case it would create a table (patient_id integer primary key, temp number) and populate it by scanning the patient table. This can obviously be indexed easily, but updating the table before a query when the source table has changed is a pain in the butt. I believe a SQL "materialized view" would do what I want, but SQLite doesn't support those; its views seem to be just syntactic sugar or macros around SELECT queries. Other than that, my best idea so far is to simplify the map/reduce updating by adding triggers on the source table that will add & remove rows from the index table. Is there any other clever way I could do this? (I've been using SQLite for 14 years and I still keep learning about more clever things it can do…) —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
