这篇博客介绍了MySQL中将Unix时间戳转换为日期格式，并展示了如何从日期字符串中截取日期部分。此外，还演示了如何在MySQL中进行列转行操作，按天统计数据。在PostgreSQL中，文章展示了如何将Unix时间戳转换为日期时间，以及从JSON字段中提取特定值，如宽度和高度。同时，提到了正则表达式在字符串处理中的应用。
mysql：unix->time
SELECT from_unixtime('1418185640') from dual;
2014-12-10 12:27:20
2,截取日期
SELECT date_format('2014-12-10 12:27:20','%y-%m-%d') from dual; 14-12-10
这个函数日期的年份只能截取两位，暂时还不知道怎么截取4位
mysql：：列转行，按天统计
SELECT k.operation,sum(b.one),sum(b.two),sum(b.three),sum(b.four),sum(b.five),sum(b.six),sum(b.seven)
from
(SELECT a.user_id,a.nick,a.user_tpye,
max(case play_data when '2016630' then cnt else 0 END ) one,
max(case play_data when '201671' then cnt else 0 END ) two,
max(case play_data when '201672' then cnt else 0 END ) three,
max(case play_data when '201673' then cnt else 0 END ) four,
max(case play_data when '201674' then cnt else 0 END ) five,
max(case play_data when '201675' then cnt else 0 END ) six,
max(case play_data when '201676' then cnt else 0 END ) seven
FROM
(select user_id,play_data,sum(count) as cnt,nick,user_tpye
from uvp GROUP BY user_id,play_data,nick,user_tpye) a
GROUP BY a.user_id,a.nick,a.user_tpye) b
LEFT JOIN odps_process.operation_kol k ON b.user_id=k.user_id
GROUP BY k.operation;
postgres
select test from test02_url limit 1;
test
-------------------------------------------------------
{ +
"streams": [ +
{ +
"index": 0, +
"codec_name": "mjpeg", +
"codec_long_name": "MJPEG (Motion JPEG)",+
"codec_type": "video", +
"codec_time_base": "1/25", +
"codec_tag_string": "[0][0][0][0]", +
"codec_tag": "0x0000", +
"width": 480, +
"height": 480, +
"coded_width": 480, +
"coded_height": 480, +
"has_b_frames": 0, +
"sample_aspect_ratio": "0:1", +
"display_aspect_ratio": "0:1", +
"pix_fmt": "yuvj420p", +
"level": -99, +
"color_range": "pc", +
"color_space": "bt470bg", +
"chroma_location": "center", +
"refs": 1, +
"r_frame_rate": "25/1", +
"avg_frame_rate": "0/0", +
"time_base": "1/25", +
"bits_per_raw_sample": "8", +
"disposition": { +
"default": 0, +
"dub": 0, +
"original": 0, +
"comment": 0, +
"lyrics": 0, +
"karaoke": 0, +
"forced": 0, +
"hearing_impaired": 0, +
"visual_impaired": 0, +
"clean_effects": 0, +
"attached_pic": 0 +
} +
} +
], +
"format": { +
"nb_streams": 1, +
"nb_programs": 0, +
"format_name": "jpeg_pipe", +
"format_long_name": "piped jpeg sequence", +
"size": "18010", +
"probe_score": 51 +
} +
}
SELECT test ->> 'streams' AS stream
FROM test02_url limit 1;
stream
-------------------------------------------------------
[ +
{ +
"index": 0, +
"codec_name": "mjpeg", +
"codec_long_name": "MJPEG (Motion JPEG)",+
"codec_type": "video", +
"codec_time_base": "1/25", +
"codec_tag_string": "[0][0][0][0]", +
"codec_tag": "0x0000", +
"width": 480, +
"height": 480, +
"coded_width": 480, +
"coded_height": 480, +
"has_b_frames": 0, +
"sample_aspect_ratio": "0:1", +
"display_aspect_ratio": "0:1", +
"pix_fmt": "yuvj420p", +
"level": -99, +
"color_range": "pc", +
"color_space": "bt470bg", +
"chroma_location": "center", +
"refs": 1, +
"r_frame_rate": "25/1", +
"avg_frame_rate": "0/0", +
"time_base": "1/25", +
"bits_per_raw_sample": "8", +
"disposition": { +
"default": 0, +
"dub": 0, +
"original": 0, +
"comment": 0, +
"lyrics": 0, +
"karaoke": 0, +
"forced": 0, +
"hearing_impaired": 0, +
"visual_impaired": 0, +
"clean_effects": 0, +
"attached_pic": 0 +
} +
} +
]
这一步把streams的信息提取出来
SELECT json_array_elements(stream::JSON) ->>'width' as width ,json_array_elements(stream::JSON) ->>'height' as height
FROM (
SELECT test ->> 'streams' AS stream
FROM test02_url limit 1
) t;
width | height
-------+--------
480 | 480
两步其实可以合成一步
select json_array_elements((test ->>'streams')::JSON) ->>'width' as width from test02_url limit 1;
两级的json嵌套，取数据的时候也需要一层一层的扒开
postres unix-time
SELECT to_timestamp(o_time_pay/1000),o_time_pay from orders
where o_time_pay is not null limit 1;
2016-04-16 18:30:37.000000 1460802637462
unix->time
SELECT date(to_timestamp(o_time_pay/1000)),o_time_pay from orders
where o_time_pay is not null limit 1;
2016-04-16 1460802637462
SELECT date(to_timestamp(a.o_time_pay/1000)),a.o_id,a.u_id,a.total::INTEGER/100 as money from
(SELECT o_time_pay,o_id,u_id,o_pay->> 'total' as total
from orders WHERE u_id>2965529 and u_id<2977597 and o_status>2
and o_pay->> 'type'＝'2'') a;
类型转换
正则
SELECT regexp_replace('12ad2df4mlq22l334', '\d+|ml', '', 'g’) addfql
odps:正则函数
SELECT k.operation,sum(b.one),sum(b.two),sum(b.three),sum(b.four),sum(b.five),sum(b.six),sum(b.seven)
from
(SELECT a.user_id,a.nick,a.user_tpye,
max(case play_data when '2016630' then cnt else 0 END ) one,
max(case play_data when '201671' then cnt else 0 END ) two,
max(case play_data when '201672' then cnt else 0 END ) three,
max(case play_data when '201673' then cnt else 0 END ) four,
max(case play_data when '201674' then cnt else 0 END ) five,
max(case play_data when '201675' then cnt else 0 END ) six,
max(case play_data when '201676' then cnt else 0 END ) seven
FROM
(select user_id,play_data,sum(count) as cnt,nick,user_tpye
from uvp GROUP BY user_id,play_data,nick,user_tpye) a
GROUP BY a.user_id,a.nick,a.user_tpye) b
LEFT JOIN odps_process.operation_kol k ON b.user_id=k.user_id
GROUP BY k.operation;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
