月度归档:2016年03月

SQL实例之六 Group By Having

SELECT SKU, StartTime, min(StartTime),sum(QuantitySold),count(id) as online FROM ebay_listings
WHERE ListingStatus = 'Active'
GROUP BY SKU HAVING min(StartTime) < "2016-03-01 01:01:01" 
AND (min(StartTime) > "2016-01-01 01:01:01" AND min(StartTime) < "2016-04-01 01:01:01")
AND sum(QuantitySold) < 1
ORDER BY min(StartTime)

首先根据SKU来进行分组,然后通过聚合函数提取一个组中的值,比如min(StartTime),取回一个组中的最小时间。搭配使用聚合函数可以当做一般查询中的Where条件类使用,只不过这里针对的是一个组而已。

以上SQL需要实现的是对一个集合进行分组,然后应用组内的最小时间进行过滤,比如最小时间是否在一个范围内。一条简单的SQL,实现了期望。

XML转换成PHP数组(或JSON)问题

例子:

<root>
   <books>
      <book>1</book>
      <book>2</book>
   </books>
</root>

//
<root>
   <books>
      <book>1</book>
   </books>
</root>

这个情况,可以转换成如下数组:

[
    “root"=>[
        "books" =>[
              ["name" => "book", "value" => 1],
              ["name" => "book", "value" => 2]
         ]
    ]
]
////
[
    “root"=>[
        "books" => ["name" => "book", "value" => 1]
    ]
]

这个转换的结果肯定不能令人满意。第一个XML books对应一个二维数组,第二个XML books对应了一维数据。换句话说,当要遍历数据时,首先要做一个判断,看它是一维数组还是多维数组。如果books只有一个子元素,那么就换成只有一个子元素的二维数组,看起来是比较方便的。不过,有时候类似books这样的元素,仅仅只能有一个子元素的时候,那么取元素还要往二维数组里面提取出来。换句话说,就仅仅从XML结构来看,无法知道books到底是仅包含一个元素,还是多个元素(当然XML本身是有提供描述支持,这里不讨论这个),那么转换函数看到仅包含一个元素的,就直接对上books,遇到多个子元素的,就对上一个二维数组,这个本身是没有问题的,只是使用起来就太不方便了。如果要解决这个问题,必须知道books到底是多元素还是单元素的,这个就是非常特定的数据结构了,无法通用。相比JSON数据结构,XML这个东西实在太笨重。

对于特定的数据结构,预先知道是对应多元素还是单元素,所以可以特别处理。以下的例子就是PHP eBay SDK中把XML转换成数组方式:

$xml = '<GetCategorySpecificsResponse xmlns="urn:ebay:apis:eBLBaseComponents">'.$cat->asXml().'</GetCategorySpecificsResponse>';
$xmlParser = new \DTS\eBaySDK\Parser\XmlParser('\DTS\eBaySDK\Trading\Types\GetCategorySpecificsResponseType');
$xmlParser->parse($xml)->toArray()

这里转换的XML输出就是规范的输出,因为对于每个元素的类型,DTS\eBaySDK\Trading\Types\GetCategorySpecificsResponseType有约定:

class GetCategorySpecificsResponseType extends \DTS\eBaySDK\Trading\Types\AbstractResponseType
{
    private static $propertyTypes = array(
        'Recommendations' => array(
            'type' => 'DTS\eBaySDK\Trading\Types\RecommendationsType',
            'unbound' => true,
            'attribute' => false,
            'elementName' => 'Recommendations'
        ),
        'TaskReferenceID' => array(
            'type' => 'string',
            'unbound' => false,
            'attribute' => false,
            'elementName' => 'TaskReferenceID'
        ),
        'FileReferenceID' => array(
            'type' => 'string',
            'unbound' => false,
            'attribute' => false,
            'elementName' => 'FileReferenceID'
        )
    );
}

这个操作确实费时费力。需要把已知的数据结构做一遍对应。

以下就是我在下载eBay类目属性时,把这个大文件进行拆分,然后按照类目ID进行存储的具体代码:

        $xmlFile = 'ebay/category-specifics-'.$site.'.xml';
        if(!\Storage::has($xmlFile)) {
            echo "文件:$xmlFile 不存在,请先下载并解压\n";
            return;
        }
        $xml = simplexml_load_file(storage_path('app/'.$xmlFile));
        
        foreach($xml->Recommendations as $cat) {
            $cid = $cat->CategoryID;
            $xml = '<GetCategorySpecificsResponse xmlns="urn:ebay:apis:eBLBaseComponents">'.$cat->asXml().'</GetCategorySpecificsResponse>';
            $xmlParser = new \DTS\eBaySDK\Parser\XmlParser('\DTS\eBaySDK\Trading\Types\GetCategorySpecificsResponseType');
            
            $cacheDir = 'ebay/site/'.$siteName."/specifics";
            $cacheFile = $cacheDir.'/'.$cid.".json";
            @mkdir(storage_path('app/'.$cacheDir), 0777, true);
            
            if(Storage::has($cacheFile)) {
                Storage::delete($cacheFile);
            }
            Storage::put($cacheFile,json_encode($xmlParser->parse($xml)->toArray()));
            unset($cid, $xml, $xmlParser, $cacheDir, $cacheFile);
        }

这样转换之后,只需要取回JSON,如果服务器端,再转换成数组就可以方便使用;对于客户端,非常简单的传递JSON字符串就可以了,根本不需要担心可以包含多元素的子元素,当仅包含一个元素时,没有被正确装换成二维数组的问题,因为这个情况都是二维数组(统一了操作)。

如果直接返回JSON,相对简单很多,当前API的开发,大多使用JSON,这个就是趋势。