09小斯
09小斯
5月前 · 24 人阅读

[Err] ERROR: CASE types integer and character varying cannot be matched

原因是数据库字段为integer数据类型而传入的是character数据类型。

用START_TIME_C可以减少发生跳变,两种情况假设:

1. 全天没有换台,这样一个人的C就会一直停留在一个节目。

2. 例如由新闻锋线承接到了辽宁新闻,再从辽宁新闻关机,这样最后一个电视台就是大连-1。

我明白了,如果以频道,就用C,包括窗口函数都用C;如果以节目,需要知道用START_TIME,因为C在用一个电视台不的不同节目不会发生变化。

第一步:根据节目来排序,然后找到第一个节目和最后一个节目,where用来除去点播台

\

报错了,这是因位没有搞懂点播台是没有记录而不是0,所以要用IS NOT NULL。但是改完之后还是没有过滤掉,还会出现点播台,这时候发现点播台P和C名称一样,用这个特点。WHERE "CHANNEL_NAME" != "PROGRAM_NAME"

去除点播台之后又有新的问题。Navicat抽风了

\

上网搜了说是什么数据权限的问题,感觉都解决不了。是不是导入数据使用的问题,是不是Postresql的问题,先不要充公去卸载重装,在MySQL里面试一下。

发现mysql出现了同样的问题,重启下电脑试一下,不行就只能重装了,重装之前记得把查询都备份一下,这可是心血。

重启之后暂时好了。

数据类型问题

运行

\

然后发现出现了问题,在导入的时候没有设置数据的格式,造成系统默认的排序出现混乱。

\

联想到之前第五章 时间 里面讲的,

\

但是我还不想用这个方法,觉得有点麻烦,想像之前MySQL里面一样转换数据格式。首先了解下postresql的时间格式。

最方便准确就是去看PostgreSQL 9.3.1 中文手册

然后要确定要修改为什么格式,确定就是你了----TIMESTAMP WITHOUT TIME ZONE

\

\

试过了在Navicat里面好像修改不了,

A USING clause must be provided if there is no implicit or assignment cast from old to new type.

大致意思是:转换类型的时候有隐含类型转换的时候,会自动转换,如果没有,那么就必须使用using指定一下转换规则。

https://blog.csdn.net/luojinbai/article/details/46387121

根据提示,在没有隐式的转换下,就需要指定Using来显示的转换。这里用到DDL。

\

结果如下:


ALTER TABLE "3月5日0-11" ALTER "START_TIME" TYPE TIMESTAMP(6) WITHOUT TIME ZONE USING "START_TIME"::TIMESTAMP(6) WITHOUT TIME ZONE

\

\

修改成功,然后把所有的表对应字段都修改。

又出现问题了,发现修改不了START_TIME_C

\

搜索全网,都是一些外国人写的。试过了各种方法都不行,换了一个表发现运行一段时间才报错,然后猛然惊醒,这个报错的意思是空值,刚好点播台就是没有这些数值的,想想能不能删除这些空值。首先找出来有多少条点播的记录,看看情况先。点播的记录不计算到收视率中,对于我没有太大的意义。

\

因为已经修改了两个表的数据结构START_TIME&END_TIME,已经不能union all 了。索性全改了。

改完发现有233421条。还挺多的,问下电视台方便能不能删除。

晚上电视台回复说暂时删除,因为这次做的目的是和传统的评价方式做对比,传统方式不含点播。

但是在这之前我已经找到了修改START_TIME_C数据类型的方法,就是在导入数据的时候修改,这样空值会被设为NULL。

\

\

将没有节目和电视台的记录删除。

\

将这几个表中的无信息数据删除。

接下来,将点播的记录删除,然后再修改START_TIME_C的数据类型。这里首先明确一下点播的特征是什么?

\

\

\

\

\

\

处理完数据后,接上前天的方法统计每个人每个人的收看轨迹。找到第一个和最后一个。

受到了启发

\

所以有了下面的代码


WITH a AS(SELECT CONCAT("CUST_ID",'-',"PATCH_CODE") as newkey, "CHANNEL_NAME", "PROGRAM_NAME", "START_TIME", "END_TIME", 
		 "DURATION", "START_TIME_C", "END_TIME_C",
		 ROW_NUMBER() OVER (PARTITION BY CONCAT("CUST_ID",'-',"PATCH_CODE") ORDER BY "START_TIME") AS ranks
FROM (SELECT * FROM "3月5日0-11" 
			UNION ALL SELECT * FROM "3月5日12-17" 
			UNION ALL SELECT * FROM "3月5日18-1930"
			UNION ALL SELECT * FROM "3月5日1930-21"
			UNION ALL SELECT * FROM "3月5日22-24") ojbk
				 )
SELECT thetime, SUM(kai) AS kai, SUM(guan) AS guan
FROM (
(SELECT "START_TIME_C" AS thetime, COUNT(*) AS kai, 0 AS guan
FROM (SELECT newkeys, "START_TIME_C"
      FROM ((SELECT newkey as newkeys, MIN(ranks) AS minranks
		FROM a
		GROUP BY newkey) b
		INNER JOIN
		a
		ON a.newkey = b.newkeys) ojbk
	WHERE minranks = ranks) as oo
GROUP BY "START_TIME_C"
ORDER BY "START_TIME_C")
UNION ALL
(SELECT "END_TIME_C" AS thetime, 0 AS kai, COUNT(*) AS guan
FROM (SELECT newkeys, "END_TIME_C"
      FROM ((SELECT newkey as newkeys, MAX(ranks) AS maxranks
		FROM a
		GROUP BY newkey) b
		INNER JOIN
		a
		ON a.newkey = b.newkeys) ojbk
	WHERE maxranks = ranks) as oo
GROUP BY "END_TIME_C"
ORDER BY "END_TIME_C")
) summary
GROUP BY thetime
ORDER BY thetime

但是这个查询存在一个问题,如果某一时间没有开机也没有关机,输出不包含这条记录。有两个解决办法:

1.在Excel中补全。运用VLOOKUP公式

公式都是正常的,但是卡在了格式上面。一直匹配不好

\

需要双击之后数据的才能匹配上,搜索

\

解决了。。。

然后每秒的太密,x轴放不下。画图不方便,所以选择每隔60s取一次数(分钟)。

少一个公式

2.为了包含从开始到最后的所有时间,我需要收集什么都没有发生的时间。构建一个新表Alltime。用LEFT JOIN

Tips: 下回导入CSV的时候能不能直接规定下那几个字段的数据类型,或者新建一个表,建表的时候规定好,然后把数据导入进来。

参考链接:PostreSQL中日期时间

PostreSQL时间日期函数

PostreSQL日期加减

PostreSQL日期加减(2)

收藏 0
time start select 点播 end newke
评论 ( 0 )