Charlie's path to dEAth

广受好评的鸟巢照

charlie — Mon, 29 Sep 2008 10:29:11 +0800

From 080917

漫画转载：网络吉祥物

charlie — Thu, 25 Sep 2008 09:34:44 +0800

from http://blogtd.org/2008/09/24/network-mascot/

“因为我跟你们一样，虽然上了网，但还是摆脱不了墙…"

blog图党是个很不错的漫画博客。

Mind Map of ITIL v3

charlie — Mon, 22 Sep 2008 10:32:39 +0800

The original content is created by the blog ITIL SERVICE MANAGEMENT on the post ITIL V3 Mind Map Download OCT 7, 2007. The author has moved the MindManager file to google pages and is downloadable following the post’s comments.

This MindManger has a filename extension of mmap. I imported it to the free mind map software FreeMind ( there’s a portable version at http://portableapps.com/node/6903) and FreeMind is so powerful to export the mindmap into a feature rich flash format. You can find it on my site, follow the link and enjoy.

http://charliezhu.com/itil/ITILV3.mmap48083.html

化学信息软件开发工具时间线

charlie — Sat, 20 Sep 2008 16:20:36 +0800

http://www.dalkescientific.com/writings/diary/archive/2008/09/20/euroqsar.html

Cross tabulation, Pivot table and OLAP Cube summarized by tools

charlie — Fri, 05 Sep 2008 22:15:28 +0800

Cross tab

Cross tabulation , is defined on WikiPedia as A cross tabulation (often abbreviated as cross tab) displays the joint distribution of two or more variables.

As below figures, the column values of the first table is transformed to column names in the second one, so the production of

Cross tabs are frequently used because:

They are easy to understand. They appeal to people who do not want to use more sophisticated measures.
They can be used with any level of data: nominal, ordinal, interval, or ratio - cross tabs treat all data as if it is nominal.
A table can provide greater insight than single statistics.
It solves the problem of empty or sparse cells.
They are simple to conduct.

I think the cross-tab is really what the Relational Database means. That’s a meaningful “Relationships” between entities but not entity and it’s attributes.

Solution by SQL Server 2005

Code:

SELECT
    SalesPerson,
    [Oranges] AS Oranges,
    [Pickles] AS Pickles
FROM
    ( SELECT SalesPerson, Product, SalesAmount FROM tblSales ) ps
PIVOT
    (
      SUM (SalesAmount)
      FOR Product IN ( [Oranges], [Pickles])
    ) AS pvt

This most weak part of feature is that you must list all pivot column name manually. It’s pain.

BOL: Using PIVOT and UNPIVOT

SELECT < non-pivoted column>,
    [first pivoted column] AS ,
    [second pivoted column] AS ,
    ...
    [last pivoted column] AS
FROM
    (< SELECT query that produces the data>)
    AS
PIVOT
(
    < aggregation function>()
FOR
[< column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS
< optional ORDER BY clause>;

Solution by MS Access

Microsoft Access provides a very nice cross table design wizard.

With the wizard, we get the cross-tab script based on JET-SQL.

TRANSFORM Sum(tblSales.SalesAmount) AS [Sum of SalesAmount]
SELECT tblSales.SalesPerson, Sum(tblSales.SalesAmount) AS [Total SalesAmount]
FROM tblSales
GROUP BY tblSales.SalesPerson
PIVOT tblSales.Product;

Pivot columns is automaticly generated and the aggregation over all pivot column can be produced. The JET-SQL is so smart and smoothly for small applications those don’t have to be scaled. You can use almost all VBA programming utilities in it. The TRANSFORM/PIVOT feature is powerful and more then 5 years before the SQL Server’s implementation.

It is based on JET-SQL, so not availible in an ADP project.

Solution by SQL Server 2000, CASE and GROUP

The classic solution of cross-tab in SQL Server 2000 or before is produced by CASE and GROUP.

select
    SalesPerson,
    SUM(CASE Product WHEN 'Pickles' THEN SalesAmount ELSE 0 END ) as [Pickles],
    SUM(CASE Product WHEN 'Oranges' THEN SalesAmount ELSE 0 END ) as [Oranges],
    SUM(SalesAmount)    as [Total Sales]
from
    tblSales
group by
    SalesPerson

We can find it’s footprint clearly in the SQL Server 2005’s pivot feature. And this is a base for other ideas.

Solution by SQL Server 2000, Dynamic SQL

Dynamic Cross-Tabs/Pivot Tables By Rob Volk

The basic idea is to generate dynamic CASE-and-GROUP based SQL statement and execute.

The following comments providing many improvement on this solution. i.e.

Introduce an WHERE statement as parameter for the @PivotColTable.
Using string to generate pivot columns but global temp table for better supporting of concurency calling.
Using user defined table with an user session column instead of global temp table.
To summarize multiple values.

There is also an similar solution I have not dived in, Dynamic Crosstab Queries, by Itzik Ben-Gan .

Solution by Excel

But the most easy and rich tool I have used is MS Excel’s pivot table.

Pivot table and OLAP Cube

The basic idea of OLAP Cube is a multi-dimession pivot table. The common visualization tool for OLAP is also a pivot table or pivot chart viewer.

So, draw a figure to show basic concepts in OLAP by example metioned above as the END.

Implementing Slope-One in T-SQL

charlie — Mon, 21 Jul 2008 05:35:44 +0800

Slope-One, the simplest form of non-trivial item-based collaborative filtering based on ratings. (Original Paper)
Referencing to Bryan O’Sullivan’s tutorial of implementing Slope One in Python, I write a the implementation in T-SQL. Believe it useful to many people and projects.

Brief process summary

Define the fact table as user data.
Calculating intermediate matrix(FreqDiff). The information about users is eliminated and frequency/ score differences data between items is produced.
Predicting from user input score with the intermediate data.

Data schema

The UserData is fact table of business transactions. I use an view to wrap it for switching between testing data and working data.
The Freq&Diff matrix is square and sparse. Only non-zero values is meaningful and stored. And a half-matrix triangle holds full information about the matrix.
There created two indices to avoid heavily bookmark-lookup.

create table UserData (                  -- fact table
    userid   varchar(50) not null,
    itemid   varchar(50) not null,
    rating   float not null default 0,
    updtime  datetime default getdate(),
    primary key (userid, itemid)
)
GO

create table FreqDiff (                  -- Freqs and Diffs
    itemid1  varchar(50),
    itemid2  varchar(50),
    freq     float not null default 0,
    diff     float not null default 0,
    updtime  datetime default getdate(),
    primary key (itemid1, itemid2)
)
GO
create index idx_freqdiff_itemid1 on FreqDiff(itemid1, freq, diff, itemid2)
create index idx_freqdiff_itemid2 on FreqDiff(itemid2, freq, diff, itemid1)

/*
 * The matrix FreqDiff is *almost* symmetric,
 * so only half of the data need to be stored.
 * There would be huge of space (50%) saved for large dataset.
 */
alter view vw_freqdiff as
select itemid1 as itemid1, itemid2 as itemid2, freq,     diff from FreqDiff fd
union all
select itemid2 as itemid1, itemid1 as itemid2, freq, -1* diff from FreqDiff fd
GO

/*
 * Wrap for userdata,
 * switch from one model to another easily.
 */
alter view vw_userdata as
select * from userdata
GO

Testing data

Same as Bryan’s but names changed for easily debugging print.

-- init userdata, Bryan O'Sullivan's sample data is used
insert into UserData values ( 'u1', 'i1',  1, getdate() )
insert into UserData values ( 'u1', 'i2', .5, getdate() )
insert into UserData values ( 'u1', 'i3', .2, getdate() )
insert into UserData values ( 'u2', 'i1',  1, getdate() )
insert into UserData values ( 'u2', 'i3', .5, getdate() )
insert into UserData values ( 'u2', 'i4', .2, getdate() )
insert into UserData values ( 'u3', 'i1', .2, getdate() )
insert into UserData values ( 'u3', 'i2', .4, getdate() )
insert into UserData values ( 'u3', 'i3',  1, getdate() )
insert into UserData values ( 'u3', 'i4', .4, getdate() )
insert into UserData values ( 'u4', 'i2', .9, getdate() )
insert into UserData values ( 'u4', 'i3', .4, getdate() )
insert into UserData values ( 'u4', 'i4', .5, getdate() )
GO

Processing the intermediate table

-- update process
delete FreqDiff
insert into FreqDiff
select
    ud1.itemid, ud2.itemid, count(*), (sum(ud1.rating - ud2.rating))/count(*), getdate()
from
    vw_userdata ud1
    join vw_userdata ud2 on
            ud1.userid = ud2.userid
        and ud1.itemid > ud2.itemid
group by ud1.itemid, ud2.itemid

Predicting

-- predict process
declare @pref table(itemid varchar(50), rating float)
insert into @pref values('i1', 0.4)

select -- distinct top 10
    itemid1,
    sum(freq)                               as freq,
    sum(freq*(diff + rating))            as pref,
    sum(freq*(diff + rating)) /sum(freq) as rating
from
    vw_freqdiff fd
    join @pref p on fd.itemid2 = p.itemid
where itemid1 not in( select itemid from @pref )
group by itemid1

Further works as intermediate data updating seems easy.
So, writing here, listening for suggestions.

“逆转瓮安”，很有创意，值得一看

charlie — Wed, 16 Jul 2008 17:00:14 +0800

Gridview排序和分页的基本机制

charlie — Wed, 09 Jul 2008 12:08:53 +0800

排序

客户端PostBack。
在Sorting事件中，取得排序的列及排序方向。
调用DataSource的排序方法。
重新DataBind

分页

Gridview读取全部的数据源数据。
在PageIndexChanging事件中，取得要前往的页编号(e.NewPageIndex)
数据绑定前，设置PageIndex属性，实现分页。

也就是说，分页完全是在ASP.NET中进行的，对于大量数据集就会有性能问题。
解：

Config VirtualBox with Host Interface Networking (HIF)

charlie — Sat, 05 Jul 2008 20:05:22 +0800

Diagram as the note.

党委、政府PK不过黑恶社会？（转载自人民网）

charlie — Sat, 05 Jul 2008 09:28:01 +0800

人民网是老胡最爱上的网，能登这种文章不容易。早晚要删，留个底子。
http://leaders.people.com.cn/GB/7468985.html
党委、政府PK不过黑恶社会？

2008年07月04日09:28 来源：人民网
【字号大中小】打印留言论坛网摘手机点评纠错
E-mail推荐:

　　6月28日下午，贵州省瓮安县城发生一起严重的围攻政府部门和打砸烧突发事件。一些人因对瓮安县公安局对该县一名女学生死因鉴定结果不满，聚集到县政府和县公安局，并点火焚烧多间办公室和一些车辆。贵州省委书记、省人大常委会主任石宗源给“6.28”事件定性为“是黑恶势力人员直接插手参与的，公然向我党委、政府挑衅的群体性事件。”但他也说“6.28”事件何以由一起单纯的民事案件酿成一起严重的打、砸、抢、烧群体性事件，也是由于诸如当地一些社会矛盾长期积累，而没有得到及时有效的解决而造成的。他也列举了这些深层次的矛盾。

　　由“6.28”事件我们可以这样的看：每次发生大规模的群体事件，不管是什么原因引起，官方的结论都是千篇一律——都不是政府的错！就石宗源给“6.28”事件下的定性结论来看也是逻辑混乱：黑恶社会凭什么挑衅政府、挑衅党委？老百姓凭什么或为什么跟着黑恶社会走而不跟着政府走？什么是黑恶社会？和党委政府作对的那是什么样的黑恶社会？有直接与政府作对的黑恶社会吗？哪一个黑恶社会不是积极主动的与政府官员勾结，拉政府官员做靠山、做保护伞而能黑、能恶的？！难道堂堂伟光大的执政党的半个多世纪的国民教化，还抵不住区区几个黑恶社会的混混的煽动有力度？唯一可解释的就是那瓮安县党委、政府黑了、恶了！否则的话就是瓮安县的老百姓黑白不分。

　　当然，黑恶社会组织的本质一定是反党、反政府、反法律的。然而，政府和党委也是由官员的个体组成的，在某种意上说与党委或和政府的一把手勾结起来和与党委、政府勾结起来的区别不是很本质。有地方党委、政府其实就是党委和政府的化身。有了党委和政府的一把手做保护伞，其实与有党委、政府撑腰而护黑、护恶没什么区别。有的时候一个地方的党政一把手黑了恶了与那里的政府黑了、恶了也没什么大区别。

　　就“6.28”的性质来讲，我不怎么同意石宗源的说法。与其说这个说法有假借黑恶社会公然挑衅政府的大帽子而为政府党委执政的无能遮羞之嫌，还不如说就是老百姓不信任政府的心态已经发展到了很严重的程度了更贴切。话又说回来，难道党委、政府不容挑衅吗？挑衅党委又犯了那条国法？黑恶社会也好，老百姓也罢，这些人又不是中共党员，挑衅党委又何罪之有？难道西方国家的反对党都是黑恶社会不成？

　　政府或执政者在面对针对政府的大规模群体事件时，不能总是说与政府作对就怎么样的恶劣或十恶不赦。中国由于封建文化的作祟，在执政者的心目中凡是反对官府的或反对政府的都是刁民。

　　现在——为什么一些黑恶社会分子就能煽动或挑动上万群众包围县政府？过去——老百姓为什么拼死也要保护中共的干部？这——才是值得执政者深思的。

　　如果执政者只是一味的要严打那些打砸烧的人，或以严厉的打击这些打砸烧的行为而吓唬或恫吓、镇压民众而达到使得民众对政府或官员敬畏有加的目的，也就是采用杀鸡吓猴的惯用做法，我想只能是越来越不灵光。反右、文革、天安门事件以及最近的周老虎这些群体事件又哪一件不是政府对不住老百姓的？哪一个不是时隔很长时间执政者才期期艾艾的认错？社会或老百姓付出的代价又是何等的大？可以说在中国社会古往今来没有哪次群体事件是错在老百姓的。屈死不告官的老话就是真实的写照。

　　不要动不动就拿与政府作对说事。与政府作对在某种意义上说也是公民社会的正当权利的一种表现形式。处处迎合政府那不是公民社会，那是封建专制社会才会有的顺民规范。当然以打砸烧的方式与政府作对那是任何社会都不允许的违法犯罪行为。但是，官员们的胡作非为，也是老百姓们冲动或不理智的根源。官员平日里又是怎样粗暴的不理性的对待老百姓的？这才是值得执政者在群体事件发生时发生后要深思不已的。否则的话，不论执政者怎样的以手中的话语权或法律来镇压群体事件，只能是扬汤止沸或抱薪救火。

　　当公民与政府发生矛盾冲突时，首先是要政府深刻反思自己的执政行为，而不是抓住群体事件中的某些违法的东东抹黑整个群体事件与老百姓的正当诉求，为自己的错误开拓，甚至倒打一耙。就像美国炸了中国驻前南大使馆后，学生们围住美国驻中国使领馆，这样的游行又何尝事先报公安机关批准过？公安机关又何尝镇压过？

　　法律不能搞机会主义、实用主义。政府不能看见与自己一致的群体事件就以胡萝卜相待，看见与自己不一致的群体事件就大棒子伺候。法律不是捏糖人儿。

　　在中国每一个群体事件的发生都不是容易的事情。中国人对待政府有天生的惧怕心理。如果不到万不得已，不到死活一个价的时候，断不会揭竿而起。正所谓官逼民反、逼上梁山。

　　至于“6.28”事件是不是逼上梁山，是不是真的逼上梁山，还是伪的逼上梁山，不管是真的、还是伪的逼上梁山，群体老百姓只要采取了或有了上梁山的做法或心态，都是执政者的失败！（东北大虫）

　　本文仅代表作者个人意见，不代表人民网观点。如需转载，请与作者本人联系。