ORACLE 语句实现计算列宽异常

[决策分析104]

环境：ORACLE 10G R2
要求：从系统表 USER_TAB_COLUMNS 里面可以取出 表名以及对应的字段名

对每个字段做如下计算 LENGTH(COLUMN_NAME)-AVG(LENGTH(COLUMN_NAME)) INTO V_LENGTH_MORE
IF V_LENGTH_MORE >0
THEN
输出大于0的这一部分数据
特殊说明：这个不需要测试数据呵呵。只要有ORACLE 环境就可以写。我自己写的有问题。所以想请教一下高手们。
真诚求教。
麻烦高手写下完整语句。谢谢！

[小灰狼W]

LENGTH(COLUMN_NAME)-AVG(LENGTH(COLUMN_NAME)) 两个值一个是没经过聚合的，一个是聚合的，不能进行运算
可以用
select length(column_name)-avg(length(column_name)over(partition by table_name) into ..

[决策分析104]

经过聚合的可以把他看做是固定数字啊。
我框架写好了。麻烦您给改下。

CREATE OR REPLACE PROCEDURE CX_TAB_COL_LENGTH_AVG IS

  V_NOTNULLID_F      T_SYS_SHUJUZLFX.NOTNULLID%TYPE;
  V_TABLENAME_F      T_SYS_SHUJUZLFX.TABLE_NAME%TYPE;
  V_NOTNULLID_S      T_SYS_SHUJUZLFX.NOTNULLID%TYPE;
  V_TABLENAME_S      T_SYS_SHUJUZLFX.TABLE_NAME%TYPE;
  V_COLUMN_LENGTH    VARCHAR2(100);
  V_AVGLENGTH        VARCHAR2(100);
  V_AVG_MINUS_LENGTH NUMBER;
  V_SQLSUBSTR        VARCHAR2(4000);

  CURSOR GETCOLUMN_AVGLENGTH IS
  
    SELECT NOTNULLID, TABLE_NAME, AVG(LENGTH(COLUMN_NAME))
      FROM T_SYS_SHUJUZLFX
     WHERE YXL <> 0
       AND COLUMN_NAME IS NOT NULL
     GROUP BY NOTNULLID, TABLE_NAME;

  CURSOR GETCOLUMN_LENGTH IS
  
    SELECT NOTNULLID, TABLE_NAME, LENGTH(COLUMN_NAME)
      FROM T_SYS_SHUJUZLFX
     WHERE YXL <> 0
       AND COLUMN_NAME IS NOT NULL;
     --GROUP BY NOTNULLID, TABLE_NAME;

BEGIN

  open GETCOLUMN_AVGLENGTH;

  loop
    fetch GETCOLUMN_AVGLENGTH
      into V_NOTNULLID_F, V_TABLENAME_F, V_AVGLENGTH;
    exit when GETCOLUMN_AVGLENGTH%notfound;
  
    open GETCOLUMN_LENGTH;
    loop
      fetch GETCOLUMN_LENGTH
        into V_NOTNULLID_S, V_TABLENAME_S, V_COLUMN_LENGTH;
      exit when GETCOLUMN_LENGTH%notfound;
    
      SELECT V_COLUMN_LENGTH - V_AVGLENGTH
        INTO V_AVG_MINUS_LENGTH
        FROM DUAL;
      IF V_AVG_MINUS_LENGTH < 0 THEN
        EXIT;
      ELSE
        V_SQLSUBSTR := 'SELECT * FROM ' || V_TABLENAME_S || ';';
        EXECUTE IMMEDIATE V_SQLSUBSTR;
      END IF;
    end loop;
    close GETCOLUMN_LENGTH;
  end loop;
  close GETCOLUMN_AVGLENGTH;

END CX_TAB_COL_LENGTH_AVG;

[决策分析104]

我写的可能不对，大家也可以按照自己的思路写。从系统表里面取值来写。
希望不要受我的思路的影响。最终实现就可以。
结果插入一张表里 或者 SELECT出来都可以。
麻烦贴下完整代码。

[yhuib]

不知道搂住到底想要啥，看看下面这个是不是楼主要的

select * 
  from (
        select t.*,avg(t.DATA_LENGTH)over(partition by t.COLUMN_NAME ) avglength 
          from user_tab_columns t 
--         where t.COLUMN_NAME=upper('colname')
       )t
 where t.DATA_LENGTH<>round(avglength)

[决策分析104]

5楼的朋友，我要的不是这个哦
可能我解释的不太好。
是这样的。 一个字段下有很多这个值得 是吧 例如数据如下：

11111
22222
33333
44444
5555555

那么我计算此列的平均宽度 再用一个值 一个值 的宽度和这个比对。
那么我就能比对出 5555555 这个值 和别的值不一样。我要把这一行 数据提取出来。就是这个意思。

[yhuib]

那你还是达不到目的的，根据你举的例子，按你的方式做的话每个都和平均数不一致，结果就取区出来了

[决策分析104]

我只要取 大于平均宽度的数据。就算不准确。其他异常宽度的都在里面。

[决策分析104]

这个在数据仓库概念里面可以被成为藏数据。需要清洗的。

[yhuib]

给你这个sql试试吧

select * 
  from
       (
        select t.*,max(cc)over() maxcc
          from (
                select t.*,count(length(t.COLUMN_NAME))over(partition by length(t.COLUMN_NAME) ) cc
                  from user_tab_columns t 
               )t
       )t
 where t.cc<>maxcc

把user_tab_columns换成你要处理的表，COLUMN_NAME换成你要处理的字段名

[决策分析104]

能解释下你的代码 意思吗？看不懂 呵呵

[yhuib]

如果只是要大于平均长度的就用

        select t.*
          from (
                select t.*,avg(length(t.COLUMN_NAME))over() avglen
                  from user_tab_columns t 
               )t
         where length(t.COLUMN_NAME)>avglen

[yhuib]

第一个sql是把和大多数长度不一样的取出来，第二个sql是把大于平均长度的取出来

[决策分析104]

12楼的朋友，我是要对字段下的每个值进行判断。
你的思路和我想的一样呵呵。

如果值 length(t.COLUMN_NAME)>avglen
满足这个情况 输出这一行数据。

[决策分析104]

顶起来。寻求高手解决

[小灰狼W]

啊，12楼的不是已经解决了吗？

[决策分析104]

我是对要对字段下的值进行判断，不是对数据类型的宽度进行判断哦。

[小灰狼W]

啊..每个字段都要这样比较吗？
判断部分就是按照12楼的改下表名和字段名就行

[决策分析104]

要每个都要改，就不用写存储过程了嘛。呵呵。批处理嘛。呵呵

[决策分析104]

顶起来，寻高手给解决办法

[决策分析104]

我是想从系统表中取出字段名和表名，然后做那个 计算。要的是结果哦。不要在表和字段名上作文章啊。