用SQLite分组计算模式

Question

我有一个带有ID (IP地址)和一个因素变量(Web浏览器)的表，我需要创建另一个表，该表对每个ID都有一个记录，还有一个因素变量的模式。我在想一些像SELECT ip, MODE (browser) FROM log GROUP BY ip这样的东西。

不幸的是，SQLite没有实现MODE函数，因此这是行不通的。我想用每个浏览器的计数构建一个临时表，然后使用SELECT DISTINCT ON或RANK ()语句，但是SQLite也不支持这些。

另外，在一个语句中这样做会更好，因为还有其他几个因素，我也需要它的模式(并且也按相同的ID分组)。

Answer 1

要计算模式，请按browser列分组，获取每个组的COUNT(*)，按该值排序，并以最大值获取记录。

如果您已经有了另一个组，请使用关联子查询：

SELECT ip,
       (SELECT browser
        FROM log AS log2
        WHERE ip = ips.ip
        GROUP BY browser
        ORDER BY COUNT(*) DESC
        LIMIT 1)
FROM (SELECT DISTINCT ip
      FROM log) AS ips

Answer 2

有一个带有时间戳、标签和延迟的日志表。我们希望看到每个标签的延迟(发送时间: ST)模式( módusz )值，并按时间戳分组。一组数据值的模式是最常出现的值。

select L, T, avg( ST ) as MODEST, C
from (
    select L, T, ST, count( ST ) as C
    from (
            select label as L, 
                         substr( substr( timeStamp, 0, 8) || '00000000', 0, 14 ) as T, 
                         latency as ST 
            from LOG 
            order by L, T, ST 
            ) as XX 
    group by L, T, ST 
) as YY
where L || '#' || T || '#' || C in (  select L || '#' || T || '#' || max(C)
                                      from(
                                        select L, T, count( ST ) as C
                                        from (
                                          select label as L, 
                                          substr( substr( timeStamp, 0, 8) || '00000000', 0, 14 ) as T, 
                                          latency as ST
                                          from LOG 
                                        ) as XX 
                                        group by L, T, ST ) as YY
                                      group by L, T )
group by L, T, C