时间戳

首页 > 解决方案 > 错误代码：1054。“字段列表”中的未知列“时间戳”

mysql - 错误代码：1054。“字段列表”中的未知列“时间戳”

问题描述

当我错了这段代码时，我得到了那个错误：

#sessions over 2 min
WITH frames AS (
  SELECT 
    user_id, date(timestamp), time(timestamp) AS starttime, 
    COALESCE(
        LEAD(time(timestamp)) OVER(PARTITION BY user_id, day(timestamp)), 
        '23:59:59'
        ) AS final
  FROM events
)

#avg and number of session over 2 min
select 
    distinct(user_id), week(timestamp) as weeks, 
    avg(time(timestamp)) as sessiontime, 
    count(timestamp) as numbersessions,
    TIMEDIFF(final, starttime) AS duration
from frames
WHERE TIMEDIFF(final, starttime) < 120
group by user_id, weeks
order by user_id, weeks;

我试图做两件事过滤不到 2 分钟的任何部分，并找到每周的平均会话长度以及每周的会话数

CREATE TABLE `events` (
    `event` varchar(50) NOT NULL,
    `user_id` varchar(50) NOT NULL,
    `timestamp` timestamp NOT NULL
);

INSERT INTO `events` (`event`, `user_id`, `timestamp`) VALUES
('Email Opened', 'ckn7zd55x001z1ilpbnh319e9', '2021-05-01 04:23:13.762'),
('Email Opened', 'ckn7zd55x001z1ilpbnh319e9', '2021-05-01 04:23:16.615'),
('Application Opened', 'ckj57u3qa003p1to980q47gdf', '2021-05-01 04:40:59.306'),
('Screen', 'ckj57u3qa003p1to980q47gdf', '2021-05-01 04:40:59.356'),
('Screen', 'ckj57u3qa003p1to980q47gdf', '2021-05-01 04:41:01.736'),
('Application Opened', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:09.01'),
('Application Updated', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:09.01'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:09.089'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:11.446'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:12.327'),
('Application Backgrounded', 'ckj57u3qa003p1to980q47gdf', '2021-05-01 04:41:18.88'),
('Application Backgrounded', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:56.556'),
('Application Opened', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:41:57.455'),
('Application Opened', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:42:04.923'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:42:04.965'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:42:07.285'),
('Screen', 'ckiumnhqu000m1ioace3lexso', '2021-05-01 04:42:08.05'),

标签： mysql

解决方案

时间戳是一个保留字，因此将其用作列名是一个非常糟糕的想法。

如果您坚持以这种方式使用它，则需要用引号括起来

推荐阅读