SQL query to 'collapse' rows which are close together

Question

We have an application that captures searches that are made by users. Because of the nature of our search (we serve results after a few characters) and the speed in which people type, we are getting a log entry for every search/letter. This looks like this:

(it looks like a up-side-down xmas tree...)

We need this data internally for counting searches (aka API calls) but for reporting to our customers it is not very nice to report on 'half' queries.

I'm looking for a way to collapse these rows into one that has the longest/last term(s) for a search.

There is a catch: a user (cid) can make more than 1 searches in a session but we can separate that if we look at timestamps I guess..

It has to be something like:

1) Group rows which are no more than 2 seconds apart

2) Order by length (or last) query to get the final query

3) Group by terms to get a count of how often a term is used to report back

Data as text:

2019-12-09  2019-12-09 12:58:45 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum cleaner
2019-12-09  2019-12-09 12:58:45 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum cleane
2019-12-09  2019-12-09 12:58:44 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum clean
2019-12-09  2019-12-09 12:58:43 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum clea
2019-12-09  2019-12-09 12:58:43 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum cle
2019-12-09  2019-12-09 12:58:42 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum cl
2019-12-09  2019-12-09 12:58:41 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum c
2019-12-09  2019-12-09 12:58:40 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuum
2019-12-09  2019-12-09 12:58:39 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacuu
2019-12-09  2019-12-09 12:58:38 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vacu
2019-12-09  2019-12-09 12:58:37 5dea585477c94502b52c43fb    92cd6cef-3ed8-4416-ac2d-cc347780b976    search  1   search  query   vac
2019-12-09  2019-12-09 12:58:15 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue widget
2019-12-09  2019-12-09 12:58:14 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue widge
2019-12-09  2019-12-09 12:58:13 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue widg
2019-12-09  2019-12-09 12:58:12 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue wid
2019-12-09  2019-12-09 12:58:12 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue wi
2019-12-09  2019-12-09 12:58:11 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue w
2019-12-09  2019-12-09 12:58:10 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blue
2019-12-09  2019-12-09 12:58:09 5dea585477c94502b52c43fb    9b41fb1d-59d2-4a12-8974-b2261b2fe484    search  0   search  query   blu
2019-12-09  2019-12-09 12:57:38 5dea585477c94502b52c43fb    f96305d9-590b-4a10-95a2-2d49a9fc63a3    search  1   search  query   widget
2019-12-09  2019-12-09 12:57:37 5dea585477c94502b52c43fb    f96305d9-590b-4a10-95a2-2d49a9fc63a3    search  1   search  query   widge
2019-12-09  2019-12-09 12:57:36 5dea585477c94502b52c43fb    f96305d9-590b-4a10-95a2-2d49a9fc63a3    search  1   search  query   widg
2019-12-09  2019-12-09 12:57:35 5dea585477c94502b52c43fb    f96305d9-590b-4a10-95a2-2d49a9fc63a3    search  1   search  query   wid

Expected result:

vacuum cleaner  1
blue widget     1
widget          1

Answer 1

A log file that looks like an upside-side-down christmas tree; it's probably safe to assume the last entries are at the top and therefore if one could grab the first record of each group then your problem would be solved, including cases where the person misspelled something and then corrected it. This renders the date and time pretty much irrelevant to finding the solution.

Assuming you field "cid" represents one search, here is a solution that is based on your data that produces exactly your expected result. The input data is expected to be in a table called "inputTable" created as:

CREATE TABLE `inputTable` (
  `date` datetime DEFAULT NULL,
  `ts` datetime DEFAULT NULL,
  `tid` varchar(255) DEFAULT NULL,
  `cid` varchar(255) DEFAULT NULL,
  `xid` varchar(255) DEFAULT NULL,
  `xvar` int(11) DEFAULT NULL,
  `ec` varchar(255) DEFAULT NULL,
  `ea` varchar(255) DEFAULT NULL,
  `ev` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Run the following with different scenarios to find those special cases where it might not work and needs to be fine tuned.

DROP PROCEDURE IF EXISTS SP_cursor1;
DELIMITER $$
CREATE PROCEDURE `SP_cursor1`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tempCid VARCHAR(255);

    DECLARE _date DATETIME;
    DECLARE _ts DATETIME;
    DECLARE _tid VARCHAR(255);
    DECLARE _cid VARCHAR(255);
    DECLARE _xid VARCHAR(255);
    DECLARE _xvar INT;
    DECLARE _ec VARCHAR(255);
    DECLARE _ea VARCHAR(255);
    DECLARE _ev VARCHAR(255);
    
    DECLARE cursor1 CURSOR for 
    SELECT * FROM inputTable;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cursor1;
    SET TempCid = "";
    DROP TABLE IF EXISTS tempTbl;
    CREATE TABLE tempTbl AS 
        SELECT * FROM inputTable WHERE 1 = 0;
    read_loop: LOOP
        fetch cursor1 into _date, _ts, _tid, _cid, _xid, _xvar, _ec, _ea, _ev;
        IF done THEN
            LEAVE read_loop;
        END IF;    
        IF tempCid <> _cid then
        INSERT INTO tempTbl
            SELECT _date, _ts, _tid, _cid, _xid, _xvar, _ec, _ea, _ev;
            SET tempCid = _cid;
        END IF; 
    END LOOP;
    CLOSE cursor1;
    SELECT ev FROM tempTbl;
    DROP TABLE tempTbl;
END$$

call SP_cursor1();

Hopefully this will help you out.

Answer 2

Not sure a time-based approach to this is most appropriate as whichever value is chosen (e.g. 2 seconds), the user may pause for longer when typing a search term. Would suggest instead just looking for the strings that are not themselves substrings of a longer search term that was typed in at a later time within the same session. This simple approach can be achieved with standard SQL:

SELECT ev,
       COUNT(ev) AS ev_count
FROM tbl t1
WHERE
  (SELECT COUNT(*)
   FROM tbl t2
   WHERE t2.cid = t1.cid
     AND t2.date <= t1.date
     AND t1.ev <> t2.ev
     AND t2.ev LIKE CONCAT(t1.ev, '%')) = 0
GROUP BY ev;

A limitation is if, for example, a user wrongly typed "vacuum cleanr" before deleting the "r" and typing it correctly, "vacuum cleanr" and "vacuum cleaner" would both be returned by the query.

See demo here (written in MySQL but ClickHouse should be similar).