<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[浅笑古今的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/u012735708</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; u012735708]]></copyright><item><title><![CDATA[hive统计页面停留时间]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/133688621</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/133688621</guid><author>u012735708</author><pubDate>Sun, 08 Oct 2023 19:50:54 +0800</pubDate><description><![CDATA[有如下字段用户uid、动作时间戳time、页面名称pn、动作名称action。思路：以用户维度按时间进行升序排列，通过lag函数找到上一个时间动作last_pn。之后统计停留时间就可以只看label =1的日志之间的时间差即可，全部代码如下，1、背景：通过业务埋点数据，统计用户在页面的停留时间。rn排序的作用是找到最后一个动作，然后进行剔除。然后将发生页面变化的节点进行标记，样例数据，样例数据存入表tmp，]]></description><category></category></item><item><title><![CDATA[mac版sublime安装SqlBeautifier]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/121861502</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/121861502</guid><author>u012735708</author><pubDate>Fri, 10 Dec 2021 17:37:24 +0800</pubDate><description><![CDATA[安装步骤

步骤1:Tools -&gt; Install Package Control 稍等片刻；
	步骤2:Sublime Text -&gt; Preferences -&gt;Package Control -&gt; install package-&gt; 搜索SQL Beautifier 稍等片刻；
	步骤3:重启Sublime；
使用方式

先按ctrl+K，再按ctrl+F，即可格式化SQL。
但是由于网络问题，以上方案可能失败，下面是离线版本sublime插件安装方案

https:/]]></description><category></category></item><item><title><![CDATA[A left join B join C 和 A left join (B join C)有什么区别]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/115185549</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/115185549</guid><author>u012735708</author><pubDate>Wed, 24 Mar 2021 19:45:01 +0800</pubDate><description><![CDATA[数据

表A

id
			city
		1
			a
		2
			b
		3
			c
		4
			d
		

表B

city
			name
		a
			x1
		b
			x2
		c
			x3
		

表C

name
			amount
		x1
			100
		x2
			200
		


WITH tmp1 AS (
SELECT 1 AS id, 'a' AS city
UNION ALL
SELECT 2 AS id, 'b' AS city
UNION ALL
SE...]]></description><category></category></item><item><title><![CDATA[PySpark将Vector拆分为列]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/111625067</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/111625067</guid><author>u012735708</author><pubDate>Thu, 24 Dec 2020 13:52:04 +0800</pubDate><description><![CDATA[一种可能的方法是转换为RDD和从RDD转换：


from pyspark.ml.linalg import Vectors

df = sc.parallelize([
    ("assert", Vectors.dense([1, 2, 3])),
    ("require", Vectors.sparse(3, {1: 2}))
]).toDF(["word", "vector"])

def extract(row):
    return (row.word, ) + tuple(row.v.]]></description><category></category></item><item><title><![CDATA[利用pyspark进行建模及调参]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/111305194</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/111305194</guid><author>u012735708</author><pubDate>Wed, 16 Dec 2020 23:32:13 +0800</pubDate><description><![CDATA[利用pyspark进行建模及调参过程如下，


from sklearn import datasets
import pandas as pd 
from pyspark.ml.evaluation import BinaryClassificationEvaluator
from pyspark.ml.tuning import ParamGridBuilder, CrossValidator
from pyspark.ml import Transformer,Pipeline
import pysp]]></description><category></category></item><item><title><![CDATA[hive通过经纬度计算两地距离]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/109672508</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/109672508</guid><author>u012735708</author><pubDate>Fri, 13 Nov 2020 12:38:54 +0800</pubDate><description><![CDATA[start_point_lng 为起点 longitude （经度）start_point_lat 为起点 latitude（纬度）

end_point_lng 为起点 longitude （经度） end_point_lat 为终点 latitude（纬度）


6378137*2*ASIN(SQRT(POWER(SIN((ta.start_point_lat-ta.end_point_lat)*ACOS(-1)/360),2) +
COS(ta.start_point_lat*ACOS(-1)/1..]]></description><category></category></item><item><title><![CDATA[HIVE中使用union报错缺少all]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/108748700</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/108748700</guid><author>u012735708</author><pubDate>Wed, 23 Sep 2020 11:05:08 +0800</pubDate><description><![CDATA[使用HIVE进行union操作，

select col1,col2 from table_name1

union

select col1,col2 from table_name2

在运行时报错，错误信息为：

ERRORError while compiling statement: FAILED: ParseException line 3:0 missing ALL at 'select' near ''
提示少了ALL。通过查询官方union的用法说明知，Hi...]]></description><category></category></item><item><title><![CDATA[hive抽取类数组型数据]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/108586502</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/108586502</guid><author>u012735708</author><pubDate>Tue, 15 Sep 2020 10:45:07 +0800</pubDate><description><![CDATA[数据样式，

user
			apps
		X123
			["123,微信,abc","234,QQ,bcd"]
		抽取apps字段中的“微信”和“QQ”，目标数据如下，

user
			apps
		X123
			QQ,微信
		思路：按","将apps字段进行拆分成行，然后在按，进行分割后取第二部分，


select 
user
,concat_ws(',', collect_set(split(app, '\\,')[1])) AS applist --从0开始计数，第二部分标号为1
fro]]></description><category></category></item><item><title><![CDATA[hive实现行转列]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/108241891</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/108241891</guid><author>u012735708</author><pubDate>Wed, 26 Aug 2020 16:11:41 +0800</pubDate><description><![CDATA[示例，

name
			label
		张三
			A
		张三
			B
		张三
			C
		

hive逻辑，


select name 
,CONCAT_WS(',', SORT_ARRAY(COLLECT_SET(label))) as label_arr
from table_name 
group by name 

结果如下，

name
			label_arr
		张三
			A,B,C
		]]></description><category></category></item><item><title><![CDATA[hive累计求和]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/108226603</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/108226603</guid><author>u012735708</author><pubDate>Tue, 25 Aug 2020 18:33:04 +0800</pubDate><description><![CDATA[需求：part为地区，amount为消费金额，按amount降序排列并对amount累计求和。

造一批数据，


SELECT '1' AS part, 11 AS amount
UNION ALL
SELECT '2' AS part, 33 AS amount
UNION ALL
SELECT '3' AS part, 22 AS amount
UNION ALL
SELECT '4' AS part, 55 AS amount
UNION ALL
SELECT '5' AS part, 44 AS ]]></description><category></category></item><item><title><![CDATA[hive中解析json数组]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/108006565</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/108006565</guid><author>u012735708</author><pubDate>Fri, 14 Aug 2020 16:10:19 +0800</pubDate><description><![CDATA[字段类型如下，

字段名
			类型
		id
			String
		orderinfo
			String
		数据示例如下，

id
			orderinfo
		1
			[{"Name":"王朝","orderDate":"2020-08-14"},{"Name":"马汉","orderDate":"2020-08-14"}]
		2
			[{"Name":"张龙","orderDate":"2020-08-14"},{"Name":"赵虎","orderDate":"2020-08-14"}]]]></description><category></category></item><item><title><![CDATA[excel中批量将unix时间转为北京时间]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/107952318</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/107952318</guid><author>u012735708</author><pubDate>Wed, 12 Aug 2020 10:46:57 +0800</pubDate><description><![CDATA[unix时间和北京时间的转换公式为：=(unix时间+8*3600)/86400+70*365+19

      
]]></description><category></category></item><item><title><![CDATA[利用python通过站长之家查询ip地址归属地]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/107633694</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/107633694</guid><author>u012735708</author><pubDate>Tue, 28 Jul 2020 13:01:07 +0800</pubDate><description><![CDATA[import sys
import os
import requests
from bs4 import BeautifulSoup
import socket
import re
import pandas as pd
import random 
import time

#匹配ip地址
def matchip (ip):
    url = "http://ip.chinaz.com/"
    try:
        url = url+str(ip)
    except:
        p.]]></description><category></category></item><item><title><![CDATA[network定制顶点颜色]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/106786394</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/106786394</guid><author>u012735708</author><pubDate>Tue, 16 Jun 2020 14:17:56 +0800</pubDate><description><![CDATA[import networkx as nx
import matplotlib.pyplot as plt
#添加顶点和边
plt.figure(figsize=(10, 10))
G_test = nx.Graph();
G_test.add_node('A')
G_test.add_node('B')
G_test.add_node('C')
G_test.add_node('D')
G_test.add_edge('A','B',weight=1)
G_test.add_edge('C','B',w.]]></description><category></category></item><item><title><![CDATA[sparkDF与pandasDF相互转化并将sparkDF存入hive]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/106743574</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/106743574</guid><author>u012735708</author><pubDate>Sun, 14 Jun 2020 11:13:37 +0800</pubDate><description><![CDATA[import pandas as pd
from pyspark.sql import SparkSession
spark= SparkSession\
                .builder \
                .appName("Dataframe") \
                .getOrCreate()

data=pd.DataFrame([[1,2],[3,4]],columns=['a','b'])
data_values=data.values.tol.]]></description><category></category></item><item><title><![CDATA[用户留存率问题计算详解]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/105867915</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/105867915</guid><author>u012735708</author><pubDate>Thu, 30 Apr 2020 21:03:34 +0800</pubDate><description><![CDATA[数据分析工作中经常会遇到计算用户留存的相关问题，例如我们现在遇到这样一个场景，

某APP,它的新增用户的次日留存、7日留存、30日留存为60%、30%、15%,请模拟出来，每天如果日新增5万用户，那么30天后，它的日活数会达到多少?

根据问题，我们已知次留，7留和月留，可以在excel里生成一个留存率预估模型。通过现有数据插入图表，然后点击图表，选择“添加元素”→“趋势线”→“指数”，针对...]]></description><category></category></item><item><title><![CDATA[SQL中如何统计员工最大连续打卡天数]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/105491148</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/105491148</guid><author>u012735708</author><pubDate>Mon, 13 Apr 2020 16:52:12 +0800</pubDate><description><![CDATA[针对员工最大连续打卡天数等类似问题SQL求解，思路如下，

示例数据如下，

id
			date
			success
		1
			2020/4/2
			1
		1
			2020/4/3
			1
		1
			2020/4/4
			1
		1
			2020/4/5
			0
		1
			2020/4/6
			1
		1
			2020/4/7
			1
		2
			2020/4...]]></description><category></category></item><item><title><![CDATA[利用python获取身份证号中年龄和性别信息]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/90672627</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/90672627</guid><author>u012735708</author><pubDate>Wed, 29 May 2019 15:57:50 +0800</pubDate><description><![CDATA[import datetime
class GetInformation(object):
     def __init__(self,id):
         self.id = id
         self.birth_year = int(self.id[6:10])
         self.birth_month = int(self.id[10:12])
         ...]]></description><category></category></item><item><title><![CDATA[Python随机数]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/89333396</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/89333396</guid><author>u012735708</author><pubDate>Tue, 16 Apr 2019 15:10:25 +0800</pubDate><description><![CDATA[这里只涉及random库。


import random

1、随机生成 0 到 1 之间的浮点数，random.random() 方法会返回 [0.0, 1.0) 之间的浮点数。


random.random()

0.7317782097442663

2、随机生成 a 与 b 之间的整数，使用 random.randint(a , b) 方法，你可以生成一个 a 与 b 之间的随机整数...]]></description><category></category></item><item><title><![CDATA[PSI指标]]></title><link>https://blog.youkuaiyun.com/u012735708/article/details/86749928</link><guid>https://blog.youkuaiyun.com/u012735708/article/details/86749928</guid><author>u012735708</author><pubDate>Sat, 02 Feb 2019 15:49:17 +0800</pubDate><description><![CDATA[公式： psi = sum(（实际占比-预期占比）* ln(实际占比/预期占比))
举个例子解释下，比如训练一个logistic回归模型，预测时候会有个类概率输出，p。在你的测试数据集上的输出设定为p1，将它从小到大排序后将数据集10等分（每组样本数一直，此为等宽分组），计算每等分组的最大最小预测的类概率值。
现在你用这个模型去对新的样本进行预测，预测结果叫p2，利用刚才在测试数据集上得到的10等...]]></description><category></category></item></channel></rss>