Icon

kn_​example_​db_​sqlite_​window_​rank_​timestamp

SQLite - Window function and Rank to detect duplicates - also using automatic ID and TimeStamps

SQLite - Window function and Rank to detect duplicates - also using automatic ID and TimeStamps
https://forum.knime.com/t/conceptual-question-keeping-history-of-updated-values-in-sqlite-store-as-separate-table/63891/2?u=mlauber71

SQLite - Window function and Rank to detect duplicates - also using automatic ID and TimeStampshttps://forum.knime.com/t/conceptual-question-keeping-history-of-updated-values-in-sqlite-store-as-separate-table/63891/2?u=mlauber71 You can 'compress' your SQLite database with the "VACUUM" commandif your SQLite database is being used by multiple clients or applications, you should make sure to lock the database beforevacuuming to prevent conflicts. You can lock the database by executing the following SQL command:PRAGMA locking_mode = EXCLUSIVE;After vacuuming is complete, you can reset the locking mode back to its default value:PRAGMA locking_mode = NORMAL; SELECT t2.* /* mark the Top row per ID as the chosen one */, CAST(CASE WHEN t2.rank_id =1 THEN 'chosen' ELSE 'duplicate' END AS TEXT) AS duplicate_type_classifierFROM /* start t2 */( SELECT * , CAST( ROW_NUMBER() OVER (/* set which unique ID should be kept - all others will be deleted *//* this makes sure even if there is a *complete* duplicate only one row will remain (and be intact) */ PARTITION BY t1.Listing/* set rules which ID/Listing should remain on Top in this case the one with the latest enry and then the lastcontact */ ORDER BY t1.my_Timestamp DESC NULLS LAST , t1.ID DESC NULLS LAST ) AS NUMERIC) AS rank_idFROM #table# AS t1) t2/* end t2 */-- WHERE duplicate_type_classifier = 'chosen'ORDER BY t2.Listing , t2.rank_id SQLite does not have a proper DATE/TIME handling - so strings and UNIX Timestampshttps://hub.knime.com/-/spaces/-/latest/~lr2kArL8kWy1rdfw/ Initialize the target table "my_tracking_table_01" *once* my_tracking_table_01DROP TABLE IF EXISTS `default`.`my_tracking_table_01`;create database_01.sqlitedata.txtCREATE TABLE IF NOT EXISTS `my_tracking_table_01` ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , $${Sv_sql_string2}$$ , my_Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL)my_insert_table_01SELECT * FROM PRAGMA_TABLE_INFO('my_tracking_table_02');=> show information about the tablemy_insert_table_01replaceChars($Price$," $","")sample.tableresults without theduplicates$Column Type$ LIKE "String*" =>"TEXT"$Column Type$ LIKE "*long*" =>"REAL"$Column Type$ LIKE "*double*" =>"REAL"$Column Type$ LIKE "*integer*" =>"INTEGER"=> can be adapted depending on the SQL databaseINSERT INTO my_tracking_table_01 ID and my_Timestampare automatically filledmy_tracking_table_02without duplicatesRANKduplicates stil thereWHERE t1.duplicate_type_classifier = 'chosen', CAST(strftime('%Y-%m-%d %H:%M:%S', my_Timestamp) AS TEXT) AS my_Timestamp_string, CAST(strftime('%s', my_Timestamp) AS INTEGER) AS my_Timestamp_unixVACUUM;SQLite provides a command to "vacuum" or "compact" your database.This command is used to free up unused space and reduce the size of the database file.my_Timestamp_stringmy_Timestamp_unixSECONDSDB Table Selector DB SQL Executor SQLite Connector CSV Reader DB SQL Executor DB Writer DB Query Reader DB Table Creator String ToNumber (PMML) String Manipulation Table Writer DB Reader Extract Table Spec Rule Engine Metanode SQLstrings DB SQL Executor DB ConnectionExtractor DB ConnectionTable Writer DB Query DB ConnectionExtractor DB Reader DB Query DB SQL Executor String to Date&Time UNIX Timestampto Date&Time SQLite - Window function and Rank to detect duplicates - also using automatic ID and TimeStampshttps://forum.knime.com/t/conceptual-question-keeping-history-of-updated-values-in-sqlite-store-as-separate-table/63891/2?u=mlauber71 You can 'compress' your SQLite database with the "VACUUM" commandif your SQLite database is being used by multiple clients or applications, you should make sure to lock the database beforevacuuming to prevent conflicts. You can lock the database by executing the following SQL command:PRAGMA locking_mode = EXCLUSIVE;After vacuuming is complete, you can reset the locking mode back to its default value:PRAGMA locking_mode = NORMAL; SELECT t2.* /* mark the Top row per ID as the chosen one */, CAST(CASE WHEN t2.rank_id =1 THEN 'chosen' ELSE 'duplicate' END AS TEXT) AS duplicate_type_classifierFROM /* start t2 */( SELECT * , CAST( ROW_NUMBER() OVER (/* set which unique ID should be kept - all others will be deleted *//* this makes sure even if there is a *complete* duplicate only one row will remain (and be intact) */ PARTITION BY t1.Listing/* set rules which ID/Listing should remain on Top in this case the one with the latest enry and then the lastcontact */ ORDER BY t1.my_Timestamp DESC NULLS LAST , t1.ID DESC NULLS LAST ) AS NUMERIC) AS rank_idFROM #table# AS t1) t2/* end t2 */-- WHERE duplicate_type_classifier = 'chosen'ORDER BY t2.Listing , t2.rank_id SQLite does not have a proper DATE/TIME handling - so strings and UNIX Timestampshttps://hub.knime.com/-/spaces/-/latest/~lr2kArL8kWy1rdfw/ Initialize the target table "my_tracking_table_01" *once* my_tracking_table_01DROP TABLE IF EXISTS `default`.`my_tracking_table_01`;create database_01.sqlitedata.txtCREATE TABLE IF NOT EXISTS `my_tracking_table_01` ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , $${Sv_sql_string2}$$ , my_Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL)my_insert_table_01SELECT * FROM PRAGMA_TABLE_INFO('my_tracking_table_02');=> show information about the tablemy_insert_table_01replaceChars($Price$," $","")sample.tableresults without theduplicates$Column Type$ LIKE "String*" =>"TEXT"$Column Type$ LIKE "*long*" =>"REAL"$Column Type$ LIKE "*double*" =>"REAL"$Column Type$ LIKE "*integer*" =>"INTEGER"=> can be adapted depending on the SQL databaseINSERT INTO my_tracking_table_01 ID and my_Timestampare automatically filledmy_tracking_table_02without duplicatesRANKduplicates stil thereWHERE t1.duplicate_type_classifier = 'chosen', CAST(strftime('%Y-%m-%d %H:%M:%S', my_Timestamp) AS TEXT) AS my_Timestamp_string, CAST(strftime('%s', my_Timestamp) AS INTEGER) AS my_Timestamp_unixVACUUM;SQLite provides a command to "vacuum" or "compact" your database.This command is used to free up unused space and reduce the size of the database file.my_Timestamp_stringmy_Timestamp_unixSECONDSDB Table Selector DB SQL Executor SQLite Connector CSV Reader DB SQL Executor DB Writer DB Query Reader DB Table Creator String ToNumber (PMML) String Manipulation Table Writer DB Reader Extract Table Spec Rule Engine Metanode SQLstrings DB SQL Executor DB ConnectionExtractor DB ConnectionTable Writer DB Query DB ConnectionExtractor DB Reader DB Query DB SQL Executor String to Date&Time UNIX Timestampto Date&Time

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2023-03-18
On NodePit since: 2023-03-19
Last update: 2024-04-30
Created with KNIME version: v4.7.1
Tags: sqlSQLitewindowrankidautomaticVACUUM

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!