数据处理 Sql Server

Code PV:

SQL Server数据库是Microsoft开发设计的一个关系数据库智能管理系统(RDBMS)，现在是全世界主流数据库之一；
SQL Server数据库具备方便使用、可伸缩性好、相关软件集成程度高等优势，能够从单一的笔记本上运行或以高倍云服务器集群为基础，或在这两者之间任何东西上运行。尽管说成“任何东西”，可是依然要考虑有关的软件和硬件配置；
SQL Server应用集成化的商务智能(BI)专用工具提供了企业级的数据管理服务。Microsoft SQL Server数据库引擎为关系型数据和结构化数据提供了更可靠安全的存储功能，使用户能够搭建和管理用于业务流程的高可用性和性能卓越的程序。
SQL Server1.0在1989年公布，迄今SQL Server已变成一个企业级的信息化平台。SQL Server2014包含内嵌的商业智能专用工具，以及一系列的分析和报告工具，能够建立数据库、备份数据、拷贝，为数据安全提供了更强的保障。

分位数

PERCENTILE_CONT和PERCENTILE_DISC都是为了计算百分位的数值，比如计算在某个百分位时某个栏位的数值是多少。
他们的区别就是前者是连续型，后者是离散型。CONT代表continuous，DISC代表discrete。PERCENTILE_CONT是连续型意味它考虑的是区间，
所以值是绝对的中间值。而PERCENTILE_DISC是离散型，所以它更多考虑向上或者向下取舍，而不会考虑区间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT purchase_monetary
  ,PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY purchase_monetary) OVER () AS num_30q  
  ,PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY purchase_monetary) OVER () AS num_70q                          
from(
    SELECT purchase_monetary
    from online_purchase
    where master_id in (10133982217,10123311972)
)tt


-- 3分位数
select purchase_monetary, purchase_monetary_per
from(
    select purchase_monetary, purchase_monetary_per
    ,row_number() over(partition by purchase_monetary_per order by purchase_monetary) rn 
    from (
        select purchase_monetary
        ,convert( decimal(18, 2) ,PERCENT_RANK() over (order by purchase_monetary)) as purchase_monetary_per
        from online_purchase
        where master_id in (10133982217,10123311972)
    )tt where purchase_monetary_per>=0.3 and purchase_monetary_per <0.4
)ttt where rn=1


--7分位数
select purchase_monetary, purchase_monetary_per
from(
    select purchase_monetary, purchase_monetary_per
    ,row_number() over(partition by purchase_monetary_per order by purchase_monetary) rn
    from (
        select purchase_monetary
        ,convert( decimal(18, 1) ,PERCENT_RANK() over (order by purchase_monetary)) as purchase_monetary_per
        from online_purchase
        where master_id in (10133982217,10123311972)
    )tt where purchase_monetary_per>=0.6 and purchase_monetary_per <=0.7
)ttt where rn=1

清空表

1
truncate table kpi_tracking_bylist

bigint转时间：

1
SELECT convert(date,DATEADD(S,1613628270481/1000 + 8 * 3600,'1970-01-01 00:00:00'))

查某张表的所有列名

1
select name from syscolumns where id = object_id('table_name');

Sql server print

1
print( CONVERT(varchar(100), DATEADD(hour,8,getdate()), 21) + ' oms_v_sales_order_newdata Start...')

表操作

1
2
3
4
5
6
7
8
-- 删列：
alter table 表名 DROP COLUMN 列名
-- 加列：
alter table 表名 ADD 列名 列类型
-- 修改列名
EXEC sp_rename '表名.[字段旧名]', '字段新名' , 'COLUMN';
-- 修改列类型
alter table 表名 alter column 字段名 decimal(18, 2)  null;

加列：

1
alter table DA.tagging_system_memdia ADD media_id BIGINT identity(1,1)

改列名:

1
EXEC sp_rename 'DA_TopRanking.kpi_tracking_bylist.plp2pdp_uv1', 'plp2pdp_pv1', 'COLUMN'

查表名对应字段和字段类型

1
2
3
4
5
6
7
8
select t1.name , CONCAT(t2.name ,'(' ,t1.prec,')') as datetype
from(
  select *
  from syscolumns
  where id = object_id('DA_Tagging.media')

)t1 left join systypes t2 on t1.xtype = t2.xtype  
where t2.[status]=0

sql server删除表里的重复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 方法一（某些数据库不支持）
delete t1
from (
  select *, row_number() over (partition by dt,platform_type order by dt,platform_type ) rn
  from DA_TopRanking.kpi_tracking_totalV2
) as t1 where rn>1

-- 方法2
select distinct *
into #temp2
from table_name

truncate table table_name
insert into  table_name
select * from #temp2

sql server 单词首字母大写

1
STUFF( name,1,1,UPPER(SUBSTRING(name,1,1)))

格式化输出时间

1
print (CONVERT(varchar(100), GETDATE(), 21))

按指定分隔符拆解字符变成列

1
2
3
4
SELECT [post_id],value 				
FROM INFORMATION_SCHEMA.TABLES
OUTER APPLY  String_Split([topics], ',')
where post_id='9ed8c860-a79e-11ea-ac76-036f1ddfe6d3'

幂函数 用于按时间降权投票分数

1
2
case when monthdiff <= 6  then post_score
      else power(0.95, (monthdiff-6)) * post_score  end

系统操作

查询数据库中对应的schema名及对应的表名 以及表类型
1
2
SELECT * FROM INFORMATION_SCHEMA.TABLES
order by table_catalog, table_schema, table_name

调用存储过程

1
2
3
4
declare @cou_num NVARCHAR(max)
declare @masteridlist NVARCHAR(max)= (select '10133982217,10123311972,10135981103')
print(@masteridlist)
exec [DA_Tagging].[SP_Look_Alike] @masteridlist,10000

向存储过程传入列表参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CREATE FUNCTION FNC_SPLIT(@MYSTR VARCHAR(500), @DELIMITER CHAR(1))
RETURNS @MYTBL  TABLE (idx smallint, value varchar(8000))
AS
BEGIN
 DECLARE @RET VARCHAR(500)
 DECLARE @INDEX INT
 DECLARE @COUNTER smallint
 --Get the first position of delimiter in the main string
 SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 SET @COUNTER = 0

 --Loop if delimiter exists in the main string
 WHILE @INDEX > 0
 BEGIN
  --extract the result substring before the delimiter found
  SET @RET = SUBSTRING(@MYSTR,1, @INDEX-1 )
  --set mainstring right part after the delimiter found
  SET @MYSTR = SUBSTRING(@MYSTR,@INDEX+1 , LEN(@MYSTR) - @INDEX )
  --increase the counter
  SET @COUNTER = @COUNTER  + 1
  --add the result substring to the table
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @RET)
  --Get the next position of delimiter in the main string
  SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 END

 --if no delimiter is found then simply add the mainstring to the table
 IF @INDEX = 0
 BEGIN
  SET @COUNTER = @COUNTER  + 1
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @MYSTR)
 END
 RETURN   
END

GO
SELECT value FROM FNC_SPLIT('10133982217,10123311972,10116946693',',')

GO
DROP FUNCTION FNC_SPLIT


----传入master_id
 select master_id,preferred_brand
   --,count(distinct master_id) as id_cnt
   from INFORMATION_SCHEMA.TABLES
   where master_id in (select v.value as user_id from String_Split('10133982217,10123311972,10116946693',',')v )

if not object_id(N'Tempdb..#T') is null
drop table #T
create table #T(user_id bigint)
insert #T
select user_id
from(
    select value as user_id
    from String_Split(@masteridlist , ',')
)t

列类别拼接 首单购买大类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select sales_member_id
, STUFF(coalesce(','+Fragrance,'')+coalesce(','+Makeup,'')
+ coalesce(','+Skincare,'')+coalesce(','+Wellness,''),1,1,'') as dragon_registered_category
   from
   (
       select sales_member_id,
         max (case item_category when 'Fragrance' then 'Fragrance' else null end ) Fragrance,
         max (case item_category when 'Makeup' then 'Makeup' else null end ) Makeup,
         max (case item_category when 'Skincare' then 'Skincare' else null end ) Skincare,
         max (case item_category when 'Wellness' then 'Wellness' else null end ) Wellness
       from(
           select sales_member_id,item_category
           from (
               select sales_member_id,sales_order_number,item_category
               ,row_number() over(partition by sales_member_id,sales_order_number order by place_time) as rn
               from INFORMATION_SCHEMA.TABLES
               where store=N'丝芙兰官网'
           ) t1  where rn=1
       )tt group by sales_member_id
   )ttt

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2024 HELLO WORLD All Rights Reserved.

UV : | PV :